Posts Tagged ‘Oracle DBA’

Duplicate Database from RMAN Backup – Oracle

April 10th, 2022, posted in Oracle Queries
Share

Duplicate Database from RMAN Backup


Seven Steps for Duplicate Database :

Step 1) RMNA Backup (Prerequisites)

Step 2) Create Password File

Step 3) Create Directory Structure

Step 4) Create Pfile

Step 5) Start Database in nomount Stage

Step 6) Run Duplicate Database Command

Step 7) Verify Duplicate Database

 

Step 1 RMAN Full database backup required (Prerequisites)

If database is in non archivelog mode then Shutdown the database and open in mount stage then take database backup i.e.RMAN > backup database;

If database is in archivelog mode use command i.e.  RMAN > backup database plus archivelog;

Backup Database using following command

Backup Database

-bash-4.1$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Mar 11 12:19:23 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys/[email protected]

connected to target database: ORCL (DBID=1611723298)

RMAN>

RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN> backup database plus archivelog;



Step 2 Create a password file for the duplicate instance.

Copy Source database password file and rename it to new database password file

Go to directory: /mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/

$ cp orapworcl orapwduporcl





Step 3 Create directory structure for Duplicate Database

Login with user db1212

Following directories need to be created

–          DupOrcl

–          Oradata

–          Fast_recovery_area

–          Diag

–          Adump

Following are sample commands

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/adump

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/oradata

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/fast_recovery_area

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/diag

-bash-4.1$





Step 4 Create Pfile with few parameters.

Login with db1212

Create Pfile with following parameters

Go to directory: /mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/

Parameters as below for initduporcl.ora,

*.audit_file_dest='/mnt/devops_0/DupDB/DupOrcl/adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.db_block_size=8192

*.db_create_file_dest='/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl'

*.db_name='duporcl'

*.db_recovery_file_dest='/mnt/devops_0/DupDB/DupOrcl/fast_recovery_area'

*.db_recovery_file_dest_size=4560m

*.diagnostic_dest='/mnt/devops_0/DupDB/DupOrcl/diag'

*.undo_tablespace='UNDOTBS1'





Step 5 Start Database in nomount stage.

-bash-4.1$ export ORACLE_SID=duporcl

-bash-4.1$

-bash-4.1$ echo $ORACLE_SID

duporcl

-bash-4.1$

SQL> startup nomount

ORACLE instance started.

Total System Global Area  222298112 bytes

Fixed Size                  2922760 bytes

Variable Size             163579640 bytes

Database Buffers           50331648 bytes

Redo Buffers                5464064 bytes

SQL>

SQL> exit




Step 6 Run Duplicate Database command.
$ rman auxiliary /

$ DUPLICATE DATABASE TO duporcl

SPFILE

set control_files='/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl'

set DB_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'

set LOG_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'

BACKUP LOCATION '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW'

;






Following is the log for above command for future references.

-bash-4.1$ rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Mar 11 12:04:18 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: DUPORCL (not mounted)

RMAN> DUPLICATE DATABASE TO duporcl

SPFILE

set control_files='/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl'

set DB_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'

set LOG_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'

BACKUP LOCATION '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW'

;

Starting Duplicate Db at 11-MAR-16

contents of Memory Script:

{

   restore clone spfile to  '/mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/spfileduporcl.ora' from

 '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/autobackup/2016_03_10/o1_mf_s_906121024_cg2639ch_.bkp';

   sql clone "alter system set spfile= ''/mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/spfileduporcl.ora''";

}

executing Memory Script

Starting restore at 11-MAR-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=13 device type=DISK

channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/autobackup/2016_03_10/o1_mf_s_906121024_cg2639ch_.bkp

channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 11-MAR-16

sql statement: alter system set spfile= ''/mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/spfileduporcl.ora''

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''DUPORCL'' comment=

 ''duplicate'' scope=spfile";

   sql clone "alter system set  control_files =

 ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_file_name_convert =

 ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment=

 '''' scope=spfile";

   sql clone "alter system set  LOG_FILE_NAME_CONVERT =

 ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment=

 '''' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

sql statement: alter system set  db_name =  ''DUPORCL'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment= '''' scope=spfile

sql statement: alter system set  LOG_FILE_NAME_CONVERT =  ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes

Variable Size                713031784 bytes

Database Buffers             352321536 bytes

Redo Buffers                   5455872 bytes

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''ORCLNEW'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''DUPORCL'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   shutdown clone immediate;

   startup clone force nomount

   restore clone primary controlfile from  '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/autobackup/2016_03_10/o1_mf_s_906121024_cg2639ch_.bkp';

   alter clone database mount;

}

executing Memory Script

sql statement: alter system set  db_name =  ''ORCLNEW'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DUPORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes

Variable Size                713031784 bytes

Database Buffers             352321536 bytes

Redo Buffers                   5455872 bytes

Starting restore at 11-MAR-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=12 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl

Finished restore at 11-MAR-16

database mounted

released channel: ORA_AUX_DISK_1

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=12 device type=DISK

contents of Memory Script:

{

   set until scn  1804148;

   set newname for datafile  1 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf";

   set newname for datafile  3 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf";

   set newname for datafile  4 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf";

   set newname for datafile  5 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf";

   set newname for datafile  6 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf";

   restore

   clone database

   ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 11-MAR-16

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_nnndf_TAG20160310T121557_cg2615wf_.bkp

channel ORA_AUX_DISK_1: piece handle=/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_nnndf_TAG20160310T121557_cg2615wf_.bkp tag=TAG20160310T121557

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05

Finished restore at 11-MAR-16

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=9 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=10 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf

contents of Memory Script:

{

   set until scn  1804148;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 11-MAR-16

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=11

channel ORA_AUX_DISK_1: reading from backup piece /mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_annnn_TAG20160310T121703_cg2637go_.bkp

channel ORA_AUX_DISK_1: piece handle=/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_annnn_TAG20160310T121703_cg2637go_.bkp tag=TAG20160310T121703

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/mnt/devops_0/OrclNew/Archives/1_11_906113933.dbf thread=1 sequence=11

channel clone_default: deleting archived log(s)

archived log file name=/mnt/devops_0/OrclNew/Archives/1_11_906113933.dbf RECID=1 STAMP=906206849

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

Finished recover at 11-MAR-16

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes

Variable Size                713031784 bytes

Database Buffers             352321536 bytes

Redo Buffers                   5455872 bytes

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''DUPORCL'' comment=

 ''Reset to original value by RMAN'' scope=spfile";

   sql clone "alter system reset  db_unique_name scope=spfile";

}

executing Memory Script

sql statement: alter system set  db_name =  ''DUPORCL'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes

Variable Size                713031784 bytes

Database Buffers             352321536 bytes

Redo Buffers                   5455872 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPORCL" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP   1 ( '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/redo01.log' ) SIZE 50 M  REUSE,

  GROUP   2 ( '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/redo02.log' ) SIZE 50 M  REUSE,

  GROUP   3 ( '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/redo03.log' ) SIZE 50 M  REUSE

 DATAFILE

  '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf'

 CHARACTER SET AL32UTF8

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/temp01.dbf";

   switch clone tempfile all;

   catalog clone datafilecopy  "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf",

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf",

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf",

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/temp01.dbf in control file

cataloged datafile copy

datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf RECID=1 STAMP=906206886

cataloged datafile copy

datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf RECID=2 STAMP=906206886

cataloged datafile copy

datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf RECID=3 STAMP=906206886

cataloged datafile copy

datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf RECID=4 STAMP=906206886

datafile 3 switched to datafile copy

input datafile copy RECID=1 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=2 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=3 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=4 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 11-MAR-16




Step 7 Check Duplicate Database Status

-bash-4.1$ ps -ef | grep pmon

db1212    8550     1  0 Mar09 ?        00:00:12 ora_pmon_orcl12c

db1212   32132     1  0 12:07 ?        00:00:00 ora_pmon_duporcl

 -bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 11 12:09:06 2016

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

——— ——————–

DUPORCL   READ WRITE

Share

How to Regenerate Reports files in Apps R12 using adadmin

December 26th, 2021, posted in Oracle EBS Application
Share
[[email protected] ~]$ cd $GL_TOP

[[email protected] 12.0.0]$ ls

admin  forms  html  lib  mds    mesg  patch    sql

bin    help   java  log  media  out   reports



[[email protected] 12.0.0]$ cd reports/



[[email protected] reports]$ ls

US

[[email protected] reports]$ cd US/

[[email protected] US]$ ls

GLCRDR.rdf    GLRTB2.rdf    GLXCLVAL.rdf  GLXRBUDA.rdf  GLXRLRUR.rdf

GLGDOCDE.rdf  GLRTBD.rdf    GLXCOCRR.rdf  GLXRCAUT.rdf  GLXRLSEG.rdf

GLGENCRE.rdf  GLRTR1.rdf    GLXDALST.rdf  GLXRCTRS.rdf  GLXRLSUS.rdf

GLGVFNAV.rdf  GLWACCTR.rdf  GLXDDA.rdf    GLXRDRTS.rdf  GLXRLTCL.rdf

GLGVOPEN.rdf  GLXACDAL.rdf  GLXDFLST.rdf  GLXRLACH.rdf  GLXRLVAT.rdf

GLRFCLD.rdf   GLXAVADT.rdf  GLXDFUSR.rdf  GLXRLBOL.rdf  GLXRSUOM.rdf

GLRFGNJ.rdf   GLXAVTRB.rdf  GLXETB.rdf    GLXRLCOA.rdf  GLXUSA.rdf

GLRGCBGT.rdf  GLXBCRBC.rdf  GLXJETAX.rdf  GLXRLFBL.rdf  GLXXTB.rdf

GLRGNJ.rdf    GLXBCSD.rdf   GLXLSLST.rdf  GLXRLHST.rdf  GLYRLBGE.rdf

GLRGNL.rdf    GLXBOR.rdf    GLXRBCR.rdf   GLXRLMAB.rdf  GLYRLGLG.rdf

GLRJED.rdf    GLXBTB.rdf    GLXRBDHR.rdf  GLXRLRFL.rdf  GLYRLJGE.rdf

GLRSGNJ.rdf   GLXCAR.rdf    GLXRBJRN.rdf  GLXRLRUD.rdf  GLYRLJRE.rdf

Take backup of two .rdf files and we will regenerate them using adadmin


[[email protected] US]$ mv GLCRDR.rdf       GLCRDR.rdf.bkp

[[email protected] US]$ mv GLYRLJRE.rdf   GLYRLJRE.rdf.bkp

[[email protected] US]$

[[email protected] US]$

[[email protected] US]$ adadmin

                     Copyright (c) 2002 Oracle Corporation

                        Redwood Shores, California, USA

                     Oracle Applications AD Administration

                                 Version 12.0.0

NOTE: You may not use this utility for custom development

      unless you have written permission from Oracle Corporation.

Your default directory is '/u01/apdba/apps/apps_st/appl'.

Is this the correct APPL_TOP [Yes] ?

AD Administration records your AD Administration session in a text file

you specify.  Enter your AD Administration log file name or press [Return]

to accept the default file name shown in brackets.

Filename [adadmin.log] :

************* Start of AD Administration session *************

AD Administration version: 12.0.0

AD Administration started at: Mon Apr 08 2013 09:00:20

APPL_TOP is set to /u01/apdba/apps/apps_st/appl

You can be notified by email if a failure occurs.

Do you wish to activate this feature [No] ?

Please enter the batchsize [1000] :

Please enter the name of the Oracle Applications System that this

APPL_TOP belongs to.

The Applications System name must be unique across all Oracle

Applications Systems at your site, must be from 1 to 30 characters

long, may only contain alphanumeric and underscore characters,

and must start with a letter.

Sample Applications System names are: "prod", "test", "demo" and

"Development_2".

Applications System Name [PROD] : PROD *

NOTE: If you do not currently have certain types of files installed

in this APPL_TOP, you may not be able to perform certain tasks.

Example 1: If you don't have files used for installing or upgrading

the database installed in this area, you cannot install or upgrade

the database from this APPL_TOP.

Example 2: If you don't have forms files installed in this area, you cannot

generate them or run them from this APPL_TOP.

Example 3: If you don't have concurrent program files installed in this area,

you cannot relink concurrent programs or generate reports from this APPL_TOP.

Do you currently have files used for installing or upgrading the database

installed in this APPL_TOP [YES] ? YES *

Do you currently have Java and HTML files for HTML-based functionality

installed in this APPL_TOP [YES] ? YES *

Do you currently have Oracle Applications forms files installed

in this APPL_TOP [YES] ? YES *

Do you currently have concurrent program files installed

in this APPL_TOP [YES] ? YES *

Please enter the name Oracle Applications will use to identify this APPL_TOP.

The APPL_TOP name you select must be unique within an Oracle Applications

System, must be from 1 to 30 characters long, may only contain

alphanumeric and underscore characters, and must start with a letter.

Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".

APPL_TOP Name [oracledb1] : oracledb1 *

You are about to use or modify Oracle Applications product tables

in your ORACLE database 'PROD'

using ORACLE executables in '/u01/apdba/apps/tech_st/10.1.2'.

Is this the correct database [Yes] ?

AD Administration needs the password for your 'SYSTEM' ORACLE schema

in order to determine your installation configuration.

Enter the password for your 'SYSTEM' ORACLE schema:

The ORACLE username specified below for Application Object Library

uniquely identifies your existing product group: APPLSYS

Enter the ORACLE password of Application Object Library [APPS] :

AD Administration is verifying your username/password.

The status of various features in this run of AD Administration is:

Saving product information.

AD code level : [B.1]

            AD Administration Main Menu

   --------------------------------------------------
  1.   Generate Applications Files menu
  1. Maintain Applications Files menu
  1. Compile/Reload Applications Database Entities menu
  1. Maintain Applications Database Entities menu
  1. Change Maintenance Mode
  1. Exit AD Administration
Enter your choice [6] : 1

         Generate Applications Files

   ----------------------------------------
  1. Generate message files
  1. Generate form files
  1.     Generate report files
  1. Generate product JAR files
  1. Return to Main Menu
Enter your choice [5] : 3

AD utilities can support a maximum of 999 workers. Your

current database configuration supports a maximum of 73 workers.

Oracle recommends that you use between 4 and 8 workers.

Enter the number of workers [4] :

Your current character set is "UTF8".

Do you want to generate Oracle Reports objects

using this character set [Yes] ?

Do you want to regenerate Oracle Reports PL/SQL library files [Yes] ?

Do you want to regenerate Oracle Reports executable files [Yes] ?

Enter list of products ('all' for all products) [all] : GL

Generate specific reports objects for each selected product [No] ?

The current set of installed languages is: US

Please select languages for generating Oracle Reports files.

You may select all of the above languages, or just a subset.

Enter list of languages ('all' for all of the above) [all] :

You selected the following languages: US

Is this the correct set of languages [Yes] ?

Reading product report information...

Selecting Oracle Reports PL/SQL library files to generate...

Selecting library files for General Ledger...

Selecting product reports to generate...

Selecting reports for General Ledger...

Generating Oracle Reports objects...

Creating the AD_UTIL_PARAMS table...

Table AD_UTIL_PARAMS already exists, so dropping the table and recreating it.

Creating FND_INSTALL_PROCESSES table...

Writing dependencies of jobs to run to appldep.txt file...

There are now 60 jobs remaining (current phase=admin+1):

    0 running, 60 ready to run and 0 waiting.

Reading completed jobs from restart file (if any).

There are now 60 jobs remaining (current phase=admin+1):

    0 running, 60 ready to run and 0 waiting.

Determining which java executables are supported by adJavaWorker...

Starting worker processes.

Worker process 1 started.

Worker process 2 started.

************* Start of AD Worker session *************

AD Worker version: 12.0.0

AD Worker started at: Mon Apr 08 2013 09:02:04

APPL_TOP is set to /u01/apdba/apps/apps_st/appl

Worker process 3 started.

************* Start of AD Worker session *************

AD Worker version: 12.0.0

AD Worker started at: Mon Apr 08 2013 09:02:04

APPL_TOP is set to /u01/apdba/apps/apps_st/appl

************* Start of AD Worker session *************

AD Worker version: 12.0.0

AD Worker started at: Mon Apr 08 2013 09:02:04

APPL_TOP is set to /u01/apdba/apps/apps_st/appl

Worker process 4 started.

Checking if all jobs have their actual and symbolic arguments in sync....

Done.

Writing jobs to run to restart file.

Reading jobs from FND_INSTALL_PROCESSES table ...

************* Start of AD Worker session *************

AD Worker version: 12.0.0

AD Worker started at: Mon Apr 08 2013 09:02:04

APPL_TOP is set to /u01/apdba/apps/apps_st/appl

Done reading jobs from FND_INSTALL_PROCESSES table ...

Telling workers to read 'todo' restart file.

Done.

Starting phase 1001 (admin+1): Generate Report Files

There are now 60 jobs remaining (current phase=admin+1):

    0 running, 60 ready to run and 0 waiting.

There are now 0 jobs remaining (current phase=Done):

    0 running, 0 ready to run and 0 waiting.

Telling workers to quit...

All workers have quit.

Dropping FND_INSTALL_PROCESSES table...

FND_INSTALL_PROCESSES table dropped.

Dropping AD_DEFERRED_JOBS table...

AD_DEFERRED_JOBS table dropped.

Copying files to destination ...

Done copying files to destination.

Review the messages above, then press [Return] to continue.

Backing up restart files, if any......Done.

Now go and check in $GL_TOP



[[email protected] ~]$ cd $GL_TOP

[[email protected] 12.0.0]$ ls

admin  forms  html  lib  mds    mesg  patch    sql

bin    help   java  log  media  out   reports



[[email protected] 12.0.0]$ cd reports/

[[email protected] reports]$ ls

US

[[email protected] reports]$ cd US/

[[email protected] US]$ ls  GLCRDR.rdf   GLYRLJRE.rdf
GLYRLJRE.rdf  GLYRLJRE.rdf

Note: Regenerating of report files is completely dependant on licensing

Share

APPS_TS_TX_DATA and APPS_TS_TX_IDX Size Keep On Increasing

May 31st, 2021, posted in Oracle Queries
Share

APPS_TS_TX_DATA and APPS_TS_TX_IDX Size Keep On Increasing

Solution:

Step1:
You can truncate these tables for deleting entire data without any back up.The truncate is more faster compare to delete command because data in the table is not write to roll back segments.

SQL>truncate table xla.XLA_DIAG_SOURCES;
SQL>truncate table xla.XLA_DIAG_EVENTS;

Step2:
Turn OFF theSLA: Diagnostics Enabled profile option at all levels.

Step3:
Check the tables, tablespace and archive logs status

Share

Workflow Notification Mailer Setup in Oracle Apps R12/12i

May 16th, 2021, posted in Oracle EBS Application
Share

On popular demand from readers, I am going to cover Workflow Notification Mailer in Oracle Applications R12/12i.
Workflow notification mailer setup in R12 is similar to 11i ( In both release 11i (OWF.H and higher in 11i) & R12 are Java Mailer)
* Previous version of Notification Mailer in 11i was based on C also called as C Mailer

Things To Note :
1. You use Oracle Application Manager (OAM) to configure Workflow Notification Mailer.
2. There are two kind of Notification (Outbound & Inbound) in Workflow Mailer
3. For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.
4. For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.
5. Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/FNDC*.txt
6. Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer ServiceWorkflow Agent Listener Service)
7. If you don’t wish to send mail notification to end user (from Dev/Test instance) then configure Test Address in configuration screen.


Step to configure Workflow Notification Mailer :

1. Login to Apps R12 with System Administrator Reponsibility
2. under Workflow : Oracle Applications Manager click on Workflow Manager

oracle apps,Oracle dba,Oracle application,oracle mail setup.oracle apps dba,

If this is first time you are configuring Workflow Notification Mailer in Oracle Apps R12/12i you will see Notification Mailers as unavailable as shown in screenshot

Click on Notification Mailers

 

oracle apps,Oracle dba,Oracle application,oracle mail setup.oracle apps dba,

 

In next screen (as shown below, click on Edit Button)

oracle apps,Oracle dba,Oracle application,oracle mail setup.oracle apps dba,

Here you have option to select Inbound notification setup or Just outgoing Notification Setup.

oracle apps,Oracle dba,Oracle application,oracle mail setup.oracle apps dba,

 

Provide SMTP Server Name (ensure that CM node should be able to connect to SMTP Server or SMTP Relay)

 

oracle apps,Oracle dba,Oracle application,oracle mail setup.oracle apps dba,

 

Uncheck Inbound Processing (from above screen), if you don’t wish to configure Inbound Notification Mailer.

If you wish to configure Inbound Notification as well then ensure IMAP Server should be configured with a valid user (create InboxProcessed Discard folder for this User)

Click on Apply button to finish configuration, at this stage Notification Mailer will test SMTP Server & IMAP Server connectivity.

For Advanced setup, click on Advanced at top right of configuration screen.

Metalink Notes for Notification Mailer
1. 453137.1 Oracle Workflow Best Practices Release 12 and Release 11i
2. 274764.1 Oracle Workflow Cartridge Workflow Java Mailer Setup Test
3. 433359.1 Tracking Workflow Notification Event Messages
4. 456921.1 Queries Related to Alert and Mailer Integration Post RUP4
5. 454706.1 How to Stop mails from Workflow Notification Mailer

Share

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