Posts Tagged ‘Oracle APPs DBA’

« Older Entries

HOW TO RECOVER DELETED ORACLE DATAFILES WITH NO DOWNTIME

April 11th, 2021, posted in Oracle Queries
Share

So you have accidentally removed a datafile from your production database? First thing, DON’T PANIC !! There’s an easy way to recover deleted datafiles, for as long as your database remains up. The procedure below works on linux, however this method conceivably can work for other platforms.

This procedure will even work if your database is in NOARCHIVELOG mode.

You may have reached this posting through Google, and in a rush to get the solution right away, so here it is.

The recovery is done in two phases.

Phase 1: instant recovery to prevent Oracle errors (like ORA-27041 “unable to open file”, ORA-01110, ORA-01116)

  1. Find the PID of DBWRITER for the right database. 
    ps -ef | grep dbw0_SID_OF_YOUR_DB
oracle   12272     1  0 10:55 ?        00:00:00 ora_dbw0_test
oracle   12941 11501  0 12:36 pts/0    00:00:00 grep dbw0_test
  2. List the deleted file handles for that DBWRITER process. 
    ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted

lrwx------  1 oracle oinstall 64 Oct 15 11:24 10 -> /home/oracle/product/10.2.0.2/dbs/lkinsttest (deleted)
lrwx------  1 oracle oinstall 64 Oct 15 11:24 23 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf (deleted)
  3. Create a symbolic link to your datafile with the original name. 
    ln -s /proc/PID_OF_DBWRITER/fd/23 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf

    That’s all. Now you are no longer going to get errors. However, if your database goes down now, you will lose that datafile for good.

Phase 2: restore the file

ARCHIVELOG database

  1. (Optional.) Issue a checkpoint. This is to reduce the recovery time when bringing the file online, depending on activity for that datafile. Unfortunately, you can’t checkpoint a single file, so the checkpoint may take some time. 
    alter system checkpoint;
  2. Backup the datafile with rman. Why rman? It’s much easier then you think. Total downtime is about one second for inactive datafiles, and more for active ones (with writes). 
    rman target /
report schema;
backup as copy datafile YOUR_DATAFILE_NUMBER format '/location_of_your_database/new_name_for_File.dbf';
sql 'alter database datafile YOUR_DATAFILE_NUMBER offline';
switch datafile YOUR_DATAFILE_NUMBER to copy;
recover datafile YOUR_DATAFILE_NUMBER;
sql 'alter database datafile YOUR_DATAFILE_NUMBER online';
exit;

NOARCHIVELOG database

  1. Make the tablespace with that datafile read only 
    select distinct tablespace_name from dba_data_files where file_name = 'YOUR_DELETED_FILE';
alter tablespace THE_TABLESPACE read only;
  2. Copy the file from the symlink to a new name 
    cp SIM_LINK_DATA_FILE NEW_NAME_FOR_DATAFILE.dbf
  3. WARNING: Ensure your copy is complete! Then, crash the database. 
    /*WAIT FOR COPY!!!*/
shutdown abort;
  4. Remove the now invalid symlink, and rename the datafile to its original name. Be careful not to remove the wrong file now — that would be a disaster: 
    rm -i SIM_LINK_DATA_FILE
mv NEW_NAME_FOR_DATAFILE.dbf SIM_LINK_DATA_FILE
  5. Startup your database normally and make the tablespace read/write. 
    startup
alter tablespace THE_TABLESPACE read write;

I hope this helps you to get out of a nasty situation.

Here’s both cases fully captured from terminal. Note, I am using Oracle-managed files. This doesn’t change the steps.

/ra5a/orabkp/test/TEST/datafile> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:31:55 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> 
SQL> select tablespace_name, file_name from dba_data_files

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------------------------------------
USERS                          /ra5a/orabkp/test/TEST/datafile/o1_mf_users_3k6xgwhb_.dbf
SYSAUX                         /ra5a/orabkp/test/TEST/datafile/o1_mf_sysaux_3k6xgwdf_.dbf
UNDOTBS1                       /ra5a/orabkp/test/TEST/datafile/o1_mf_undotbs1_3k6xgwg9_.dbf
SYSTEM                         /ra5a/orabkp/test/TEST/datafile/o1_mf_system_3k6xgwd4_.dbf
EXAMPLE                        /ra5a/orabkp/test/TEST/datafile/o1_mf_example_3k6xjdjw_.dbf
LOST                           /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf

6 rows selected.

SQL> select tablespace_name, table_name from dba_tables where owner = 'TESTING';

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
LOST                           LOST_TABLE

SQL> connect testing/testing
SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

/ra5a/orabkp/test/TEST/datafile> ls -lF
total 1015132
-rw-r-----  1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
-rw-r-----  1 oracle oinstall 104865792 Oct 15 12:22 o1_mf_lost_3k74mq08_.dbf
-rw-r-----  1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r-----  1 oracle oinstall 503324672 Oct 15 12:32 o1_mf_system_3k6xgwd4_.dbf
-rw-r-----  1 oracle oinstall  20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r-----  1 oracle oinstall  26222592 Oct 15 12:32 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r-----  1 oracle oinstall   5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf

/ra5a/orabkp/test/TEST/datafile> rm o1_mf_lost_3k74mq08_.dbf
/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:35:24 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;
select count(*) from lost_table
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6:
'/ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> 
SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

/ra5a/orabkp/test/TEST/datafile> ps -ef|grep dbw0_test
oracle   12272     1  0 10:55 ?        00:00:00 ora_dbw0_test
oracle   12941 11501  0 12:36 pts/0    00:00:00 grep dbw0_test
/ra5a/orabkp/test/TEST/datafile> ls -l /proc/12272/fd|grep deleted
lrwx------  1 oracle oinstall 64 Oct 15 11:24 10 -> /home/oracle/product/10.2.0.2/dbs/lkinsttest (deleted)
lrwx------  1 oracle oinstall 64 Oct 15 12:17 26 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf (deleted)

/ra5a/orabkp/test/TEST/datafile> ln -s /proc/12272/fd/26 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf

/ra5a/orabkp/test/TEST/datafile> ls -lF
total 912620
-rw-r-----  1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
lrwxrwxrwx  1 oracle oinstall        17 Oct 15 12:37 o1_mf_lost_3k74mq08_.dbf -> /proc/12272/fd/26
-rw-r-----  1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r-----  1 oracle oinstall 503324672 Oct 15 12:32 o1_mf_system_3k6xgwd4_.dbf
-rw-r-----  1 oracle oinstall  20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r-----  1 oracle oinstall  26222592 Oct 15 12:32 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r-----  1 oracle oinstall   5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf

/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:38:18 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> 
SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

/ra5a/orabkp/test/TEST/datafile> rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Mon Oct 15 12:39:48 2007

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

connected to target database: TEST (DBID=1934173752)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    480      SYSTEM               ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_system_3k6xgwd4_.dbf
2    25       UNDOTBS1             ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_undotbs1_3k6xgwg9_.dbf
3    230      SYSAUX               ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_sysaux_3k6xgwdf_.dbf
4    5        USERS                ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_users_3k6xgwhb_.dbf
5    150      EXAMPLE              ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_example_3k6xjdjw_.dbf
6    100      LOST                 ***     /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /ra5a/orabkp/test/TEST/datafile/o1_mf_temp_3k6xj9xn_.tmp

RMAN> backup as copy datafile 6 format '/ra5a/orabkp/test/TEST/datafile/lost.dbf';

Starting backup at 2007-10-15 12:40:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf
output filename=/ra5a/orabkp/test/TEST/datafile/lost.dbf tag=TAG20071015T124045 recid=13 stamp=636036046
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2007-10-15 12:40:46

RMAN> sql 'alter database datafile 6 offline';

sql statement: alter database datafile 6 offline

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "/ra5a/orabkp/test/TEST/datafile/lost.dbf"

RMAN> recover datafile 6;

Starting recover at 2007-10-15 12:41:07
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 2007-10-15 12:41:07

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN> 

Recovery Manager complete.

/ra5a/orabkp/test/TEST/datafile> ls -lF
total 1015132
-rw-r-----  1 oracle oinstall 104865792 Oct 15 12:41 lost.dbf
-rw-r-----  1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
lrwxrwxrwx  1 oracle oinstall        17 Oct 15 12:37 o1_mf_lost_3k74mq08_.dbf -> /proc/12272/fd/26
-rw-r-----  1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r-----  1 oracle oinstall 503324672 Oct 15 12:38 o1_mf_system_3k6xgwd4_.dbf
-rw-r-----  1 oracle oinstall  20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r-----  1 oracle oinstall  26222592 Oct 15 12:38 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r-----  1 oracle oinstall   5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf

/ra5a/orabkp/test/TEST/datafile> rm o1_mf_lost_3k74mq08_.dbf
/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:42:03 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from lost_table;

  COUNT(*)
----------
     50070
Share
read more no comments

APP-PAY-06153:System Error. Procedure INIT_FORMS on Step 1

October 4th, 2020, posted in Oracle EBS Application
Share

APP-PAY-06153:System Error. Procedure INIT_FORMS on Step 1

 

Issue :

APP-PAY-06153:System Error. Procedure INIT_FORMS at Step 1 :

N:->Human Resources->Work Structures->Location

Oracle Application, Oracle Error,Oracle DBA,Oracle Apps DBA,APPS DBA

Solution :

N: System Administrator->Profile->System

Click find

Assign Site: FA: Vision Operations

Save and close

N:->Human Resource->Work Structure->Organization

 

Form is opened

Share
read more no comments

Decoded INST_TOP Oracle R12 INSTANCE_HOME

June 13th, 2020, posted in Oracle
Share

 

Features on this R12 INST_TOP

-All the logs files have been moved from APPL_TOP,ORACLE_HOME to this to top

  • All configuration files have been moved to INST_TOP
    like
    DBc file
    All the http related config files
    appsweb.cfg
    All the start or stop scripts
    all the OC4J related config files

-we can now share the Apps and technology stack among multiple nodes and even instances (Dev,Test, Production) as all the log and config are located in this top.We will have INST_TOP for each context.

-Configuration and log files are co-located

-Easier collection of instance state
-Minimize disk usage and maximize performance

Filesystem look

/inst/apps/ ($INST_TOP)
• /admin
• /scripts ($ADMIN_SCRIPTS_HOME) All the start and stop are here
• /appl ($APPL_CONFIG_HOME) All the apps env are here
• /fnd/12.0.0/secure ($FND_SECURE) dbc file lis located here
• /certs
• /logs ($LOG_HOME) all the logs are located
• /ora ($ORA_CONFIG_HOME)
/10.1.2
/10.1.3 ($ORACLE_CONFIG_HOME) all the techstack(oacore,forms) related config files
• /pids
• /portal
• /rgf

Some more changes to variable

OA_HTML =$COMMON_TOP/webapps/oacore/html
JAVA_TOP=$COMMON_TOP/java/classes
AF_JLIB=$COMMON_TOP/java/lib
JAVA_BASE=$COMMON_TOP/java/

Advantage  of INST_TOP

-Clear distinction between the shared file system and the file system unique to an instance
-Ability to share code (Apps, technology stack) among multiple  nodes and even instances (Development ,Test, Production)
-Enables split ownership of applications and technology stack file systems
-Improves code safety by making shared file system read-only while not patching
-Configuration and log files are co-located
-Easier collection of instance state
-Ability to use network or local storage for various file system portions
-Minimize disk usage and maximize performance

R12.2 Changes

-R12.2 contains the Fusion Middleware 11g tech-stack. The logfile and configuration files is located in that Oracle Home

–Concurrent Manager log/out files have been moved in Non editioned File system

Where to Put INST_TOP in shared file system

It is recommended to put INST_TOP on local filesystem  in shared APPL_TOP filesystem  architecture. This is because of the Apache mutex problem.Apache performance is highly sensitive to mutex file access latency, and at higher loads is also sensitive to I/Os per second. In case you have INST_TOP on shared filesystem, it is suggested to point these context variable to local file-system

s_lock_pid_dir
s_pids_dir
s_web_pid_file

 

 

Conclusion

Hope you like the post on R12 INST_TOP, all the advantages and R12.2 changes.  Please do provide the feedback.

 

 

Share
read more no comments

Create Oracle EBS Application Custom Top

June 6th, 2020, posted in Oracle
Share

Share
read more no comments

Function To Convert Number To Arabic Number

April 28th, 2019, posted in Oracle Queries
Share
Function To Convert Number To Arabic Number,Arabic Number,English Arabic Number,Oracle Numbers,Oracle DBA,Oracle Apps DBA,Oracle Coding,Oracle Arabic Number Query
FUNCTION convert_to_arabic (p_input IN VARCHAR2, p_msg OUT VARCHAR2)
    RETURN VARCHAR2
    IS
    --

    l_output VARCHAR2(20) DEFAULT NULL;
    CURSOR csr_convert (cp_value VARCHAR2)

    IS

    SELECT TRANSLATE (cp_value,'0123456789',

                         UNISTR ('\0660')
                      || UNISTR ('\0661')
                      || UNISTR ('\0662')
                      || UNISTR ('\0663')
                      || UNISTR ('\0664')
                      || UNISTR ('\0665')
                      || UNISTR ('\0666')
                      || UNISTR ('\0667')
                      || UNISTR ('\0668')
                      || UNISTR ('\0669')
                    ) arabic_numerals
       FROM DUAL;

    BEGIN


    IF p_input IS NOT NULL THEN

      
        OPEN csr_convert(p_input);
        FETCH csr_convert INTO l_output;
        CLOSE csr_convert;

    END IF;

    RETURN l_output;
    EXCEPTION     
    WHEN OTHERS THEN
        IF csr_convert%ISOPEN THEN
            CLOSE csr_convert;

        END IF;
      
       p_msg := SUBSTR(SQLERRM,1,200);
       RETURN l_output;

    END convert_to_arabic;

 

Share
read more no comments
« Older Entries