Friday, August 28, 2009

adcri commands

# using adcri to purge logs older than 31 days

[prod-oranode-30:+ASM1]$ pwd
/oralogs/diag
[prod-oranode-30:+ASM1]$ du -sk *
56324 asm
12268 rdbms
51336 tnslsnr
[prod-oranode-30:+ASM1]$ adrci

ADRCI: Release 11.1.0.7.0 - Production on Fri Aug 28 13:49:23 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ADR base = "/opt/oracle/product"
adrci> SET HOMEPATH diag/asm/+asm/+ASM1
adrci> PURGE -age 44640
adrci> exit
[prod-oranode-30:+ASM1]$ du -sk *
35108 asm
12268 rdbms
51336 tnslsnr

# running purge scripts

# purge.adrci
SET HOMEPATH diag/rdbms/pd01ref/pd01ref1
PURGE -age 44640
SET HOMEPATH diag/asm/+asm/+ASM1
PURGE -age 44640

adrci script=purge.adrci

Thursday, August 27, 2009

11g - automatically changes audit_trail to OS

# 11g - automatically changes audit_trail to OS when you open a readonly standby database.

Thu Aug 27 11:50:26 2009
alter database open
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access

Active standby database test

SQL> alter database open;

Database altered.

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> connect oradba/oradba
Connected.
SQL> select * from ACTIVE_STANDBY_TEST;

ID
----------
1

# run an insert on PROD then switch logs, and recovery applies the transaction on the active standby

SQL> select * from ACTIVE_STANDBY_TEST;

ID
----------
1
2

updateNodeList after cloning ORACLE_HOME

# After cloning the ORACLE_HOME there are still references to previous node names when you run opatch. Need to clean up the inventory first.

cd $ORACLE_HOME/oui/bin
./runInstaller -updateNodeList ORACLE_HOME=/opt/oracle/product/11.1.0/db_1 CLUSTER_NODES=test-oranode-00

Wednesday, August 26, 2009

ORA-29702: error occurred in Cluster Group Service operation

# problem

SQL> startup nomount;
ORA-29702: error occurred in Cluster Group Service operation

29702, 00000, "error occurred in Cluster Group Service operation"
// *Cause: An unexpected error occurred while performing a CGS operation.
// *Action: Verify that the LMON process is still active. Also,
// check the Oracle LMON trace files for errors.


# cause - cloned a RAC ORACLE_HOME to a server with no CRS

# solution

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk rac_off
make -f ins_rdbms.mk ioracle

Tuesday, August 25, 2009

Upgrade rman catalog for 11.1.0.7.0 databases

rman

Recovery Manager: Release 11.1.0.7.0 - Production on Tue Aug 25 08:25:59 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: P01REF (DBID=3918657745)

RMAN> connect catalog rcowner/password@rman

connected to recovery catalog database
PL/SQL package RCOWNER.DBMS_RCVCAT version 10.02.00.04 in RCVCAT database is too old

RMAN> upgrade catalog;

recovery catalog owner is RCOWNER
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 11.01.00.07
DBMS_RCVMAN package upgraded to version 11.01.00.07
DBMS_RCVCAT package upgraded to version 11.01.00.07

RMAN>

Tuesday, August 18, 2009

alter tablespace read only hangs.

# I ran the following, but it hung
alter tablespace ORDERAUDIT_PAR10_06 read only;

# I ran diagnostics to identify the transctions (this is not really needed)
SQL> SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND SQL_TEXT LIKE 'alter tablespace%'; 2 3 4

SQL_TEXT
--------------------------------------------------------------------------------
SADDR
----------------
alter tablespace ORDERAUDIT_PAR10_06 read only
070000010EB2D260


SQL> SELECT SES_ADDR, START_SCNB
FROM V$TRANSACTION
ORDER BY START_SCNB; 2 3

SES_ADDR START_SCNB
---------------- ----------
070000010EAF4420 453671129
070000010EB2D260 455278874
070000010EB2D260 455278877

SQL> SELECT T.SES_ADDR, S.USERNAME, S.MACHINE
FROM V$SESSION S, V$TRANSACTION T
WHERE T.SES_ADDR = S.SADDR
ORDER BY T.SES_ADDR 2 3 4
5 ;

SES_ADDR USERNAME
---------------- ------------------------------
MACHINE
----------------------------------------------------------------
070000010EAF4420 BKO_SERVER
CMC\CFDFXNFR-APP04

070000010EB2D260 SYS
test-oranode-40

070000010EB2D260 SYS
test-oranode-40

# The blocking session was clearly identified in dba_blockers view. (we could use this to schedule an application restart)
1* select sid, username, program, machine from v$session where sid in (select holding_session from dba_blockers)
SQL> /

SID USERNAME
---------- ------------------------------
PROGRAM
------------------------------------------------
MACHINE
----------------------------------------------------------------
1586 BKO_SERVER
jCFDFXNFRBackoffice1.exe
CMC\CFDFXNFR-APP04

# I killed the session, and the "alter tablespace" command completed.
SQL> select 'alter system kill session '''||sess.sid||', '||sess.serial#||''';' from v$session sess where sid in (select holding_session from dba_blockers);

'ALTERSYSTEMKILLSESSION'''||SESS.SID||','||SESS.SERIAL#||''';'
--------------------------------------------------------------------------------
alter system kill session '1586, 1177';

SQL> alter system kill session '1586, 1177';

System altered

Friday, August 14, 2009

Not enough space to delete a file ?

[test-oranode-00.cmc.local:sl02dwh1]$ rm o1_mf_temp_4p1ykwhf_.tmp
rm: 0653-609 Cannot remove o1_mf_temp_4p1ykwhf_.tmp.
There is not enough space in the file system.

Thursday, August 13, 2009

Removing disks from an ASM disk group

1 select group_number,name from v$asm_disk
2* order by group_number,name
SQL> /

GROUP_NUMBER NAME
------------ ------------------------------
1 ASM_ORADATA31_0000
1 ASM_ORADATA31_0001
1 ASM_ORADATA31_0002
1 ASM_ORADATA31_0003
1 ASM_ORADATA31_0004
1 ASM_ORADATA31_0005
2 ASM_ORADATA32_0000
2 ASM_ORADATA32_0001
2 ASM_ORADATA32_0002
2 ASM_ORADATA32_0003
2 ASM_ORADATA32_0004
2 ASM_ORADATA32_0005

12 rows selected.

SQL> ALTER DISKGROUP ASM_ORADATA31 DROP DISK ASM_ORADATA31_0005;

Diskgroup altered.

SQL> ALTER DISKGROUP ASM_ORADATA32 DROP DISK ASM_ORADATA32_0005;

Diskgroup altered.

SQL> select est_minutes from V$ASM_OPERATION;

EST_MINUTES
-----------
6

Thursday, August 6, 2009

ORA-00328: archived log ends at change 35826448060, need later change 35829206909

# OAO dataguard was behind. Recovery was running, but it was not doing anything. When recovery was stopped and started it reported :-

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
Thu Aug 6 13:36:57 2009
Recovery interrupted!
Thu Aug 6 13:36:57 2009
Errors in file /opt/oracle/product/admin/pl01oao1/bdump/pl01oao1_mrp0_14017.trc:
ORA-00328: archived log ends at change 35826448060, need later change 35829206909
ORA-00334: archived log: '+ASM_ORADATA226/pl01oao/archivelog/2009_08_06/thread_2_seq_5570.2263.694186543'
Thu Aug 6 13:36:57 2009

# Seems like this was caused by a network glitch while sending the log, or a restart on the standby.

Thu Aug 6 11:34:32 2009
Errors in file /opt/oracle/product/admin/pd01oao2/bdump/pd01oao2_lns1_3510.trc:
ORA-03113: end-of-file on communication channel
Thu Aug 6 11:34:32 2009
LGWR: I/O error 3113 archiving log 22 to 'pl01oao'
Thu Aug 6 11:36:43 2009
Beginning log switch checkpoint up to RBA [0x15c3.2.10], SCN: 35826448061
Thu Aug 6 11:36:43 2009
Thread 2 advanced to log sequence 5571 (LGWR switch)
Current log# 23 seq# 5571 mem# 0: +ASM_ORADATA216/pd01oao/onlinelog/group_23.264.685470871
Current log# 23 seq# 5571 mem# 1: +ASM_ORADATA211/pd01oao/onlinelog/group_23.270.685470873
Thu Aug 6 11:36:43 2009
LNS: Failed to archive log 22 thread 2 sequence 5570 (3113)

# Seems like there was data missing from an archivelog, and recovery was waiting for that data. (not a gap, so FAL server did not help)

I tried deleting the archivelog in ASM, running crosscheck, starting recovery, but the bad log was still online in a standby redo log, and recovery of the standby redo log failed.

# Fixed by clearing the standby redo log group and restarting recovery.

SYS@pl01oao1>alter database clear logfile group 24;

Database altered.

Monday, August 3, 2009

IPC vs TCP

I did some quick tests to compare performance of TCP database links vs IPC database links to see if it would make sense to change the communication between databases on the same server. Both tests indicated IPC performance was better than TCP, but only by 6-7%.

Details on Oracle IPC can be found in Metalink Note 29232.1.

I ran two tests and recorded transaction runtimes :-

Test 1. insert into local table select 301049 rows

LOCAL source table :- 00:00:01.93
IPC database link :- 00:00:02.16 (11% slower than local table)
TCP database link :- 00:00:02.32 (20% slower than local table, 7.5% slower than IPC database link)

Test 2. a plsql loop doing 301049 inserts into a local table

LOCAL source table :- 00:02:01.79
IPC database link :- 00:02:11.74 (5% slower than local table)
TCP database link :- 00:02:24.18 (10% slower than local table, 6% slower than IPC database link)

# Test 1
# tnsnames.ora
NFR_CFD_TCP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=test-vip-10)(PORT=1587))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NFR_CFD)
)
)


NFR_CFD_IPC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = NFR_CFD))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NFR_CFD)
)
)

# listener.ora

LISTENER_TEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = NFR_CFD))
)
)

SID_LIST_LISTENER_TEST =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = nfr_cfd)
(SID_NAME = tl01cfd1)
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_2)
)
)

create database link NFR_CFD_TCP connect to testuser identified by xxx using 'NFR_CFD_TCP';
create database link NFR_CFD_IPC connect to testuser identified by xxx using 'NFR_CFD_IPC';

create table local as select * from dba_source where rownum < 2;
create table ipc as select * from dba_source where rownum < 2;
create table tcp as select * from dba_source where rownum < 2;

create or replace procedure local_proc as
cursor source is select * from sys.dba_source;
begin
for i in source loop
insert into local values (i.owner, i.name, i.type, i.line,i.text);
end loop;
end;
/
show errors;

create or replace procedure tcp_proc as
cursor source is select * from sys.dba_source@NFR_CFD_TCP;
begin
for i in source loop
insert into tcp values (i.owner, i.name, i.type, i.line,i.text);
end loop;
end;
/
show errors;

create or replace procedure ipc_proc as
cursor source is select * from sys.dba_source@NFR_CFD_IPC;
begin
for i in source loop
insert into ipc values (i.owner, i.name, i.type, i.line,i.text);
end loop;
end;
/
show errors;

ORADBA @tl01cfd1> insert into LOCAL select * from sys.dba_source;

301049 rows created.

Elapsed: 00:00:01.93

Statistics
----------------------------------------------------------
2927 recursive calls
55101 db block gets
31185 consistent gets
3 physical reads
43444304 redo size
836 bytes sent via SQL*Net to client
739 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
301049 rows processed

ORADBA @tl01cfd1> insert into IPC select * from sys.dba_source@NFR_CFD_IPC;

301049 rows created.

Elapsed: 00:00:02.16

Statistics
----------------------------------------------------------
2922 recursive calls
64791 db block gets
11236 consistent gets
2 physical reads
43444528 redo size
836 bytes sent via SQL*Net to client
749 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
301049 rows processed

ORADBA @tl01cfd1> insert into TCP select * from sys.dba_source@NFR_CFD_TCP;

301049 rows created.

Elapsed: 00:00:02.32

Statistics
----------------------------------------------------------
2912 recursive calls
64776 db block gets
11241 consistent gets
2 physical reads
43448712 redo size
836 bytes sent via SQL*Net to client
749 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
301049 rows processed

truncate table local;
truncate table tcp;
truncate table ipc;

# Test 2

ORADBA @tl01cfd1> exec local_proc

PL/SQL procedure successfully completed.

Elapsed: 00:02:01.79
ORADBA @tl01cfd1> exec ipc_proc

PL/SQL procedure successfully completed.

Elapsed: 00:02:11.74
ORADBA @tl01cfd1> exec tcp_proc

PL/SQL procedure successfully completed.

Elapsed: 00:02:24.18