Oracle APPS DBA : October 2012

DATABASE BACKUP USING RMAN

Hot backup means, taking the backup of the database while it is up running.

1. DB_HOT_BACKUP Script

vi Backuprman.rc

---------------------------------------

run

{
allocate channel chnl1 device type disk;
allocate channel chnl2 device type disk;
backup database;
backup archivelog all;
backup current controlfile;
release channel chnl1;
release channel chnl2;
}

----------------------------------------

$ rman target sys/<sys passwd> @Backuprman.rc

Note :

1) here i have not specified any location for backup so it goes to default location. For details, please run “show all” command so that you will come to know where backup will go.

2) ensure that “controlfile autobackup is on;” If not please change the same using following command @ RMAN prompt.

CONFIGURE CONTROLFILE AUTOBACKUP ON;

2. Compressed DB_HOT_BACKUP Script

run

{

allocate channel T1 type disk format 'E:\SPMATBACKUP\%d_%T_%t_%s';

allocate channel T2 type disk format 'E:\SPMATBACKUP\%d_%T_%t_%s';

backup as COMPRESSED BACKUPSET check logical database;

release channel T1;

release channel T2;

allocate channel T1 type disk format 'E:\SPMATBACKUP\ARCH_%T_%t_%s';

backup as COMPRESSED BACKUPSET archivelog all not backed up;

release channel T1;

}

allocate channel for maintenance type disk;

delete noprompt obsolete device type disk;

release channel;

Copying a package from one Schema to Another

The following script shows how to copy the package from one schema to another:

1.Source Database run the below script

SQL>

SET PAGESIZE 10000

SET feedback OFF

SET heading OFF

SET echo OFF

spool \Package.sql

SELECT TEXT

FROM USER_SOURCE

WHERE NAME = 'MY_PACKAGE';

spool OFF

SET echo ON

SET feedback ON

SET heading ON

Copy the Package.sql file from source to destination using scp

scp Package.sql root@testdatabase2.com:/oracle/

2.Destination Database(testdatabase2)

SQL>

@d:\Package.sql

Steps to run Autoconfig in Oracle Apps R12 adautocfg.sh

Autoconfig is tool/utility to reconfigure your Oracle Application configuration files using context (XML file) and template files

This post covers steps to run Autoconfig in Oracle Apps R12 (

A) Running Autoconfig on R12 environment Application Tier

1. Login as user owning application tierfor R12 (usually applmgr)

2. Set environment variable by executing env file $INSTALL_BASE/ apps/ apps_st/ appl/ APPL[$SID]_[$hostname].env

3. cd $ADMIN_SCRIPTS_HOME (or $INSTALL_BASE/apps/$CONTEXT_NAME/ admin/ scripts)

4. run adautocfg.sh (Autoconfig script)
./adautocfg.sh5. Supply apps password when prompted.
.
B) Running Autoconfig on R12 environment Database Tier

1.Login as user owning database tier for R12 (usually oracle)

2. Set environment variable by executing env file $INSTALL_BASE/ db/ tech_st/ [11.1.0 or 10.2.0]/ [$SID]_[$hostname].env

3. cd $ORACLE_HOME/ appsutil/ scripts/ $CONTEXT_NAME

4. run adautocfg.sh (Autoconfig script)
./adautocfg.sh

5. Supply apps password when prompted

Things good to know about Autoconfig in R12
1. Autoconfig logs for R12 application tier are at

$INSTALL_BASE/inst/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log ($INST_TOP/admin/log/[$MMDDhhmm])

2. Autoconfig logs for R12 Database tier are at

[$RDBMS_ORACLE_HOME]/appsutil/log/[$CONTEXT_NAME]/[$MMDDhhmm]/adconfig.log

3. R12 system is autoconfig enabled and uses context file stored in [INST_TOP]/appl/admin/[CONTEXT_NAME].xml (Application Tier)
and
[$DATABASE_ORACLE_HOME]/appsutil/[$CONTEXT_NAME].xml (Database Tier)

Related

387859.1 Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12

Tracking the Oracle Database Growth

SQL>
select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage tsu , dba_hist_tablespace_stat ts ,dba_hist_snapshot sp, dba_tablespaces dt where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days ) a
) b GROUP BY b.tsname ORDER BY b.tsname;

Output :

TABLESPACE_NAME CUR_USED_SIZE_MB AVG_INCREAS_MB
------------------------------ ---------------- --------------
APPS_TS_ARCHIVE 6080.5 0
APPS_TS_INTERFACE 872.13 0
APPS_TS_MEDIA 38483 0
APPS_TS_NOLOGGING 188.25 0
APPS_TS_QUEUES 414.25 0
APPS_TS_SEED 2725.75 0
APPS_TS_SUMMARY 670 0
APPS_TS_TOOLS .13 0
APPS_TS_TX_DATA 55249 726.15
APPS_TS_TX_IDX 50169.63 774.18
APPS_UNDOTS1 3484.19 329.85

TABLESPACE_NAME CUR_USED_SIZE_MB AVG_INCREAS_MB
------------------------------ ---------------- --------------
CTXD 11.72 0
INTERIM .13 0
ODM 9.56 0
OLAP 15.69 0
OWAPUB .08 0
PORTAL .47 0
PROD_BIPLATFORM 2 0
PROD_MDS 4.44 0
SYSAUX 1790.81 -2.01
TAXWARE 422.06 0
XXYDL 3.06 0

22 rows selected.

Concurrent Program creation and add it to Request Group

Overview:

Develop a report or PL/SQL Package to register as a concurrent program
Create Executable: Link it to Report file(.rdf) or PL/SQL Package created
Create Concurrent Program: Link to it executable defined in previous step
Enter Parameters and link Value Sets
Assign the registered Concurrent Program to a request group

Creating Executable:

Navigation: Login into Oracle Applications –> Go to Application Developer Responsibility –> Concurrent –> Executable

Here we will register oracle report as a concurrent program

FIELDS:

Executable: This is User Understandable Name
Short Name: This is Unique and for system reference
Application: Under which application you want to register this Conc. Program
Description: Description
Execution Method: Based on this field, your file has to be placed in respective directory or database.
Execution File Name: This is the actual Report file name. If you register a PL/SQL Procedure in a package you have to give the packagename.procedure. You don’t need to specify any parameters in procedure here.

Action: Save

Create Concurrent Program:

Navigation: Application Developer –> Concurrent –> Program

FIELDS:

Program: User Understandable Program Name
Short Name: This should be unique name and for system reference
Application: Enter the application under which you want to register this conc.prog
Executable Name: Enter the User Understandable Executable Name
Method: This will be populated automatically from Executable Definition
Output Format: Select the format of the output you want
Output Style: Select A4 to print on A4 Paper
Printer: You can default any printer or you can enter while submitting concurrent program.

DEFINE PARAMETERS AND VALUE SETS

Navigation: click on Parameters button in above screen

FIELDS:

Seq: It’s always better to enter sequences in multiple of 5 or 10. So that you can insert any additional parameters if you want later in middle.
Parameter: Name the Parameter Field. This is for system reference
Description: You can see this description while submitting the conc.prog.
Value set: “15 Characters” is the standard value set for Character input parameters
Default Type: This field is not mandatory. If you want to default any particular value to save time while submitting the concurrent program you can do so here.
Prompt: This is the actual message displayed while submitting the conc.prog
Token: This is used to link this parameter to the parameter defined in actual report file(.rdf)