Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Wednesday, April 20, 2011

ORA-01567: dropping log

recently my dev which was a standalone got refreshed from prod which was 3 node RAC. When I try to drop the redo log groups I was getting below error:

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED
6 2 UNUSED
7 3 ACTIVE
8 3 UNUSED
9 3 UNUSED
10 3 UNUSED


dropped couple of log groups:

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED
7 3 ACTIVE
8 3 UNUSED

Now since this is a standalone db I don't need threads 2 & 3 so when I tried to drop group from thread 3:

SQL> alter database drop logfile group 8;
alter database drop logfile group 8
*
ERROR at line 1:
ORA-01567: dropping log 8 would leave less than 2 log files for instance i3
(thread 3)
ORA-00312: online log 8 thread 3:
'+ICMDEV_FRA_01/icmdev/onlinelog/group_8.261.748372139'
ORA-00312: online log 8 thread 3:
'+ICMDEV_DATA_01/icmdev/onlinelog/group_8.373.748372177'


Hmm Crazy...

I need to first disable the thread before deleting the logs.


SQL> alter database disable thread 3;

Database altered.

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED
7 3 INACTIVE
8 3 UNUSED

6 rows selected.

SQL> alter database drop logfile group 8;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED



Also if the group you want to delete is current and you disabled the thread# of that group.

say.

SQL> select thread#,group#,status from v$log;

THREAD# GROUP# STATUS
---------- ---------- ----------------
2 1 CURRENT
2 2 INACTIVE
3 3 INACTIVE
3 4 CURRENT
1 11 INACTIVE
1 12 CURRENT


Here thread 3 and group 4 is current and now I without archiving I have disabled the thread# 3.



SQL> alter database disable thread 3;

Database altered.

SQL> select thread#,group#,status from v$log;

THREAD# GROUP# STATUS
---------- ---------- ----------------
2 1 CURRENT
2 2 INACTIVE
3 3 INACTIVE
3 4 INACTIVE
1 11 INACTIVE
1 12 CURRENT

6 rows selected.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance UNNAMED_INSTANCE_3 (thread 3) needs to be archived
ORA-00312: online log 4 thread 3:
'+DBNAME_REDO_01/dbname/onlinelog/group_4.263.762210781'
ORA-00312: online log 4 thread 3:
'+DBNAME_REDO_02/dbname/onlinelog/group_4.264.762210783'

Here it comes, since group4 was not archived I cannot just drop the group here.
So how do we overcome this.
SQL> alter database clear unarchived logfile group 4;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select thread#,group#,status from v$log;

THREAD# GROUP# STATUS
---------- ---------- ----------------
2 1 CURRENT
2 2 INACTIVE
1 11 INACTIVE
1 12 CURRENT



All is well now the thread# 3 is dropped.

Thursday, April 14, 2011

Duplicate Target Database in 11gR2

In 11gR2 oracle made lot of changes & one of the great feature is: we don't have to connect to either Target or Catlog database while cloning anymore. We can clone now by just connecting to the auxiliary instance.

Get the auxiliary instance in nomount.

#!/bin/ksh
ORACLE_HOME=
ORACLE_SID=ICMDEV
export ORACLE_HOME
export ORACLE_SID
$ORACLE_HOME/bin/rman msglog rest1.log append< connect auxiliary /
run
{
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
allocate auxiliary channel aux3 type disk;
allocate auxiliary channel aux4 type disk;
DUPLICATE DATABASE TO MYCLONE UNTIL TIME "TO_DATE('04/10/2011 22:32:00','MM/DD/YYYY HH24:MI:SS')" BACKUP LOCATION 'where the backup is';
release channel aux1;
release channel aux2;
release channel aux3;
release channel aux4;
}
EOF



Now after the clone I had a strange issue while querying dba_temp_files:

SQL> SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM DBA_TEMP_FILES
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 513 - see DBWR trace file
ORA-01110: data file 513: '+ICMDEV_DATA_01'


In Alert log:
ORA-01157: cannot identify/lock data file 513 - see DBWR trace file
ORA-01110: data file 513: '+ICMDEV_DATA_01'
Errors in file /icm01/u0001/oracle/diag/rdbms/icmdev/ICMDEV/trace/ICMDEV_j000_12561.trc: - TRACE FILE SHOWS some query related to grid control

---- Checked all the datafiles are online and consistent
select * from v$datafile where file#=513;

select * from v$tempfile where file#=513;
gives no result ---

SQL> alter database datafile 513 offline;
alter database datafile 513 offline
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "513"


Since I am querying the DBA_TEMP_FILES I know for sure something is not right with the temporary tablespaces.

select TABLESPACE_NAME,CONTENTS from dba_tablespaces a where a.contents like '%TEMP%';

TABLESPACE_NAME CONTENTS
TEMP1 TEMPORARY
TEMP2 TEMPORARY
GCTEMP TEMPORARY


SQL>
SQL> CREATE TEMPORARY TABLESPACE TEMP3;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP3;

Database altered.

SQL> SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM DBA_TEMP_FILES
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 513 - see DBWR trace file
ORA-01110: data file 513: '+ICMDEV_DATA_01'


SQL> DROP TABLESPACE TEMP1;

Tablespace dropped.

SQL> SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM DBA_TEMP_FILES
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 513 - see DBWR trace file
ORA-01110: data file 513: '+ICMDEV_DATA_01'


SQL> DROP TABLESPACE TEMP2;

Tablespace dropped.

SQL> DROP TABLESPACE TEMP;
DROP TABLESPACE TEMP
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist


SQL> SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM DBA_TEMP_FILES
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 517 - see DBWR trace file
ORA-01110: data file 517: '+ICMDEV_DATA_01'


GCTEMP 8192 1048576 1048576 1 2147483645 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO HOST NO
SQL> DROP TABLESPACE GCTEMP;

Tablespace dropped.

SQL> SELECT * FROM DBA_TEMP_FILES;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
+ICMDEV_DATA_01/icmdev/tempfile/temp3.367.748374189
6 TEMP3 104857600 12800 ONLINE
1 YES 3.4360E+10 4194302 12800 103809024 12672


SQL> EXIT

So after dropping the GCTEMP tablespace, the above query worked fine. My guess is during the backup gc temp file might have some contents which were not recorded.








Friday, April 1, 2011

Find Scheduled concurrent programs

Here is the query through which we can find all the scheduled concurrent programs:

select a.request_id, b.user_concurrent_program_name,c.concurrent_program_name,a.requested_start_date, c.EXECUTION_METHOD_CODE,
d.execution_file_name,d.execution_file_path
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_concurrent_programs c,
apps.FND_EXECUTABLES d
where status_code in ('Q','I')
and a.concurrent_program_id = b.concurrent_program_id
and b.concurrent_program_id = c.concurrent_program_id
and c.application_id=d.application_id
and c.executable_id=d.executable_id
and a.requested_start_date > SYSDATE
and a.hold_flag = 'N'
order by 1;


also look at the below notes for further understanding:
How to Determine Scheduled Concurrent Requests [ID 170107.1]
What are the Meaning of the Codes in the STATUS_CODE and PHASE_CODE Columns of FND_CONCURRENT_REQUESTS Table? [ID 152209.1]
Status Code Incorrect for Scheduled Requests in FND_CONCURRENT_REQUESTS Table [ID 1054419.6]