From 11g Pfile can be created from memory

SQL> create pfile=’/tmp/init.ora’ from memory;

File created.

SQL> !vi /tmp/init.ora
# Oracle init.ora parameter file generated by instance XXXX2 on 10/04/2012 23:40:45

baskar.l

Hi,

It has been more than a year, i havent blogged. I love to post my learning,errors and solution which i do face daily on a regular basis which i will do starting today. Hope the information i share are useful to all.

thanks,

baskar.l

Hi,

Little flashback on the situation. A query was running for couple of hours and sqltuning advisory showed creating a index will improve the performance of the query.

Tried creating the index, since it has taken lot of time the session was killed. Trying to drop the index it said..

 SQL> drop index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1;
drop index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1
                      *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

But..still that sid was active..

SQL> @sid
Enter value for sid: 1243
old   3: where sid = ‘&sid’
new   3: where sid = ’1243′

       SID    SNO OSUSER     USERNAME        STATUS   PROCESS      PADDR            SPID         MODULE
———- —— ———- ————— ——– ———— —————- ———— —————————————-
      1243  44678 astlprd    APPS            ACTIVE   14131        000000057B93DE68 2240         JSWUS_MATL_RECEIPT_ACCOUNT_RPT

1 row selected.

so i did

SQL> @kill
Enter value for sid: 1243
Enter value for sno: 44678
old   1: alter system kill session ‘&sid,&sno’
new   1: alter system kill session ’1243,44678′

System altered.

and tried to drop but it said

SQL> drop index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1;
drop index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1
                      *
ERROR at line 1:
ORA-08104: this index object 1892315 is being online built or rebuilt

After this i cleaned up the failed session

SQL> conn /as sysdba
Connected.
SQL> DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
OBJECT_ID := 1892315;
WAIT_FOR_LOCK := NULL;
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (OBJECT_ID);
COMMIT;
END;
/  2    3    4    5    6    7    8    9   10   11

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

then that object doesnt exist

SQL> @obj
Enter value for 1: JSWUS_MATL_RECEIVING_TEMP_N1
old   3: where object_name like upper(‘%&1%’)
new   3: where object_name like upper(‘%JSWUS_MATL_RECEIVING_TEMP_N1%’)

no rows selected

and then created the indexes

SQL> create index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1 on JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP(“RECEIPT_NUM”,”ORGANIZATION_ID”,”TRANSACTION_TYPE”);

Index created.

SQL> exec fnd_stats.gather_index_stats(‘JSW_CUSTOM’,'JSWUS_MATL_RECEIVING_TEMP_N1′);

PL/SQL procedure successfully completed.

happy learning,

baskar.l

ORA-14450: attempt to access a transactional temp table already in use

Hi, Its a GTT table with oncommit delete rows..

SQL> ALTER TABLE developer.YYJSW_VASIND_TOTAL_DESP
ADD
(
TOP_COLOR             VARCHAR2(50),
BOTTOM_COLOR          VARCHAR2(50)
);  2    3    4    5    6
ALTER TABLE developer.YYJSW_VASIND_TOTAL_DESP
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
SQL>
SQL> select owner,object_id from dba_objects where owner=’DEVELOPER’ and object_name=’YYJSW_VASIND_TOTAL_DESP’;

OWNER                           OBJECT_ID
—————————— ———-
DEVELOPER                          770078

SQL> select * from v$locked_object where object_id = 770078;

no rows selected

SQL> select * from v$Lock where id1 = 770078;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE
—————- —————- ———- — ———- ———- ———-
   REQUEST      CTIME      BLOCK
———- ———- ———-
000000021F9BF7A8 000000021F9BF7C8        518 TO     770078          1          3
         0     106617          0
SQL> @sid
Enter value for sid: 518

old   3: where sid = ‘&sid’
new   3: where sid = ’518′

       SID    SERIAL# OSUSER
———- ———- ——————————
USERNAME                       STATUS   PROCESS      PADDR
—————————— ——– ———— —————-
SPID         MODULE
———— ————————————————
       518       8872 Abhishek?Pandey
APPS                           INACTIVE 3744:2300    000000021F61FE60
6374         T.O.A.D.
SQL> SQL>
SQL>
SQL> @kill
Enter value for sid: 518
Enter value for sno: 8872
old   1: alter system kill session ‘&sid,&sno’
new   1: alter system kill session ’518,8872′

System altered.

SQL> ALTER TABLE developer.YYJSW_VASIND_TOTAL_DESP
ADD
(
TOP_COLOR             VARCHAR2(50),
BOTTOM_COLOR          VARCHAR2(50)
);  2    3    4    5    6

Table altered.

Thanks,

baskar.l

When i executed the below query in 10.2.0.4  i got the error as

SQL> select down_time
from (select text_line, lag(text_line) over(order by 1) down_time from log)
where text_line like ‘%Shutting down instance (immediate)%’  2    3
4  /
select down_time
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

And in the alert log it showed

Thu May 27 15:20:13 2010
Errors in file /orahome.11/oracle/product/STEELP/10203/admin/STEELP/udump/steelp1_ora_18859.trc:
ORA-07445: exception encountered: core dump [kudmnli()+319] [SIGSEGV] [Address not mapped to object] [0x000000038] [] []

After raising an SR with oracle and got a solution as to set the correct ORA_NLS10 setting for 10.2.0.4

First it was

[osteelp@jswbdbp16 bdump]$ echo $ORA_NLS10
/orahome.11/oracle/product/STEELP/10203/nls/data/9idata

and after changing the ORA_NLS10 to below setting

[osteelp@jswbdbp16 bdump]$ export ORA_NLS10=$ORACLE_HOME/nls/data
[osteelp@jswbdbp16 bdump]$ echo $ORA_NLS10
/orahome.11/oracle/product/STEELP/10203/nls/data

the script got executed..

SQL> select down_time
from (select text_line, lag(text_line) over(order by 1) down_time from log)
where text_line like ‘%Shutting down instance (immediate)%’  2    3  ;

DOWN_TIME
——————————————————————————–
Fri Apr 23 07:48:56 2010
Mon May  3 06:41:11 2010
Mon May  3 13:44:45 2010
Tue May  4 06:51:55 2010
Mon May 17 06:36:49 2010

happy learning,

baskar.l

hi,

Recently i faced this issue…

While opening the database

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01187: cannot read from file 1 because it failed verification tests
ORA-01110: data file 1:
‘/xxx/data/system01.dbf’

In alert log it shows

ORA-01186: file 16 failed verification tests
ORA-01122: database file 16 failed verification check
ORA-01110: data file 16: ‘/xxx/data/a_ref03.dbf’
ORA-01206: file is not part of this database – wrong database id
File 16 not verified due to error ORA-01122

Solution.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1265844 bytes
Variable Size             432017228 bytes
Database Buffers          629145600 bytes
Redo Buffers               11313152 bytes
Database mounted.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 5965182104509 generated at 05/28/2010 07:17:43 needed for
thread 1
ORA-00289: suggestion :
/xxx/data/archive/1_1_720170170.dbf
ORA-00280: change 5965182104509 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>

SQL> alter database open resetlogs;

Database altered.

happy learning,

baskar.l

Hi All,

Below is one of the error occured when logging in to 115.10.2 self service web application and when clicking on the responsibility it shows

Oracle Forms Web CGI: Error detected
Your request cannot be serviced at this time due to the following error:

ERROR: URL Contains One or More Restricted Characters

Solution:

After many tests found that “If there is a space in defined in the responsibility key for a responsibility then the above error occurs.

Responsbility key should be like ‘BAS_TEST_ORDER’  and not ‘BAS TEST ORDER’ (spaces should not be there,use underscore)

Happy Learning,

baskar.l

Hi all,

The below script will delete the applied logs in the DR site by keeping the last ten applied logs.

set echo on
select database_role from v$database;
var v_maxseq1 number
var v_maxseq2 number
exec select max(sequence#)-10 into :v_maxseq1 from v$archived_log where applied=’YES’ and thread#=1;
exec select max(sequence#)-10 into :v_maxseq2 from v$archived_log where applied=’YES’ and thread#=2;
print v_maxseq1
print v_maxseq2
set echo off
set heading off
set feedback off
spool script.rman
select ‘delete archivelog until sequence ‘ || :v_maxseq1 || ‘thread 1 ;’ from dual;
select ‘delete archivelog until sequence ‘ || :v_maxseq2 || ‘thread 2 ;’ from dual;
spool off
host rman target / @script.rman
exit
spool off
exit

The above script will avoid setting the RMAN configuration of applied on standby.

Happy Learning,

baskar.l

Concatenating a new value to the existing values in a table
SQL> create table baskar(name varchar2(50),sal number(35));

Table created.

SQL> insert into baskar values (‘bas,kar,sun,ben’,1000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from baskar;

NAME                                                      SAL
————————————————– ———-
bas,kar,sun,ben                                          1000

Now i need to append a name ‘RAM’ to the existing name  without changing or updating  the entire value which can be done by

SQL> update baskar set name=name||’,ram’;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from baskar;

NAME                                                      SAL
————————————————– ———-
bas,kar,sun,ben,ram                                      1000

Happy learning.

baskar.l

Hope Everyone is doing great. Today 27th Mar 2010 am starting this blog to share my knowledge with all the oracle community members.Your views on the post and comments are much appreciated.

thanks
baskar.l