Environment Variables from database table - Oracle E-Business Suite

Are you running Oracle E-Business Suite (EBS) / Applications and want to get an operating system level environment variable value from a database table, for example for use in PL/SQL? Or perhaps to default a concurrent program parameter? Didn't think environment variables were stored in the database?

Try out out this query that shows you $FND_TOP:

Environment variable queries

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

query that shows you $FND_TOP:

select value

from   fnd_env_context

where  variable_name = 'FND_TOP'

and    concurrent_process_id = 

      ( select max(concurrent_process_id) from fnd_env_context );

 

 

Product "TOP" directories e.g the full directory path values from fnd_appl_tops under APPL_TOP?

 

col variable_name format a15

col value format a64

select variable_name, value

from   fnd_env_context

where  variable_name like '%\_TOP' escape '\'

and    concurrent_process_id = 

     ( select max(concurrent_process_id) from fnd_env_context )

order by 1;

 full directory path to $APPLTMP?

 select value

from   fnd_env_context

where  variable_name = 'APPLTMP'

and    concurrent_process_id = 

      ( select max(concurrent_process_id) from fnd_env_context );

NB: These queries assume your concurrent managers are running!

Important Changes in Autoconfig R12.2

With Oracle E-Business Suite Release 12.2, OC4J has been replaced with Oracle WebLogic Server. This has resulted in a reduced role for AutoConfig in the configuration of the Oracle HTTP Server and the oacore, oafm, forms and forms-c4ws services.This article talks about changes in Autoconfig R12.2

Up to and including Oracle E-Business Suite Release 12.1.3, AutoConfig was used to manage the entire Oracle HTTP Server configuration and OC4J instance configuration.
In Oracle E-Business Suite Release 12.2, it manages only a part of the Oracle HTTP Server configuration. It also only partially manages the configuration of the oacore, oafm, forms and forms-c4ws services. The remaining scope of AutoConfig remains the same as prior to Oracle E-Business Suite Release 12.2.

Obtaining Database PSU history from SQL

Check for ORACLE Patches applied

It is possible to determine all Patch Set Updates that has been applied to a database using a simple SQL statement - but note that this will only retrieve the details of patchsets that contained a database element (i.e. you had to run a SQL script in as part of the patch).

Note: It is better to use the opatch utility to retrieve the details of all patches and patchsets applied to an environment. However, this query may still prove useful.

AS GRID and 11g:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed  | grep -i 'PSU'

FOR DATABASE

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | egrep 'PSU|PATCH SET UPDATE'

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i 'DATABASE PSU'

 CRS

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i 'TRACKING BUG' | grep -i 'PSU'

GI:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i 'GI PSU'

QUERY the registry$history database object

select substr(action_time,1,30) action_time,

substr(id,1,10) id,

substr(action,1,10) action,

substr(version,1,8) version,

substr(BUNDLE_SERIES,1,6) bundle,

substr(comments,1,20) comments

from registry$history;

col Time for a18
col target for a30
col action for a30

select to_char(action_time,'DD-MON-YYYY HH24:MI:SS') time
     , namespace||' '||version target
     , action||' '||comments action
  from dba_registry_history;

REDO LOGFILE SIZING

Redo Log Files and Sizing  

set heading off;

select '******************************************************' from dual;

select '****           Redo Log Files and Sizing          ****' from dual;

select '******************************************************' from dual;

timing start 'Redo Sizing';

set heading on;

col "File Name" for a60;

col "Size in MB" format 999,999,999,999,990

select a.group#, thread#, substr(a.member,1,80) as "File Name",b.bytes/1024/1024 as "Size in MB" from v$logfile a,v$log b where a.group#=b.group#;

timing stop 'Redo Sizing';

Redo log switch History

Redo log switch History

Redo log switch History

Find out  date  & time, SCN and other details about log switch

-- this is to set date format

sql >alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';

-- this is to check redo log switch history

sql >
    col f format a3
    col switch_time form a15
    col first_change# format 999999999999

    select b.thread#,

           b.sequence#,

           b.first_time,

           trunc( ( e.first_time ) -

                  ( b.first_time ) ) days,

           to_char( trunc(sysdate) +

                    ( ( e.first_time ) -

                      ( b.first_time ) ),

                    'hh24:mi:ss' ) switch_time,

          decode( 15/1440, greatest( 15/1440,

                           ( e.first_time ) -

                           ( b.first_time ) ),

                           '*' ) f,

          e.first_change# - b.first_change# net_change,

          b.first_change#

     from v$loghist b, v$loghist e

    where e.sequence#(+) = b.sequence# + 1

      and e.thread#(+) = b.thread#

   order by ( b.first_time ) asc;

Oracle 11g R2 RAC Database creation Manually

Oracle 11g R2 RAC Database creation Manually.

Follow the below steps to create RAC database.

1.create pfile:

*.audit_file_dest='/u01/oradb/admin/u/adump'
*.audit_trail='db'
#*.cluster_database=true
#*.cluster_database_instances=2
*.compatible='11.2.0.4.0'
*.control_files='+DATADG/PUNITDB/control01.ctl','+DATADG/PUNITDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PUNIT'
*.db_unique_name='PUNITDB'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/oradb'
PUNITDB1.instance_number=1
PUNITDB2.instance_number=2
PUNITDB1.instance_name='PUNITDB1'
PUNITDB2.instance_name='PUNITDB2'
#*.log_archive_config='dg_config=(PUNITDB,PUNITSB)'
*.log_archive_dest_1='location=+ARCHIVEDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PUNITDB'
#*.log_archive_dest_2='SERVICE=PUNITSB_DGSRVC ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PUNITDB'
#*.log_archive_dest_state_2='ENABLE'
*.memory_max_target=1000M
*.memory_target=900M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
#*.standby_file_management='AUTO'
PUNITDB1.thread=1
PUNITDB2.thread=2
*.undo_management='AUTO'
PUNITDB1.undo_tablespace='UNDOTBS1'
PUNITDB2.undo_tablespace='UNDOTBS2'

2.create required directories in disk group and file system.

ASMCMD [+DATA01] > mkdir PUNITDB
ASMCMD [+DATA01] > cd PUNITDB
ASMCMD [+DATA01/PUNITDB] >

3.create password file.

orapwd file=orapwPUNITDB1 password=****** entries=20



4.prepare database creation script.

createdb.sql
===========


CREATE DATABASE PUNIT
USER SYS IDENTIFIED BY ******
USER SYSTEM IDENTIFIED BY ******
DATAFILE '+DATADG/PUNITDB/ PUNITDB_system_01.dbf' 
SIZE 1G AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '+DATADG/PUNITDB/PUNITDB_sysaux_01.dbf' 
SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 3G
DEFAULT TABLESPACE USERS DATAFILE '+DATA01/PUNITDB/PUNITDB_users_01.dbf' 
SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP1 TEMPFILE '+DATA01/PUNITDB/PUNITDB_temp1_01.dbf' 
SIZE 3G AUTOEXTEND ON MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '+DATA01/PUNITDB/PUNITDB_undo11_01.dbf' 
SIZE 10M REUSE AUTOEXTEND ON NEXT 5M  MAXSIZE UNLIMITED
LOGFILE GROUP 1 ('+DATA01/PUNITDB/PUNITDB_REDO_1_01.rdo','+DATA01/PUNITDB/PUNITDB_REDO_1_02.rdo') SIZE 50M,
GROUP 2 ('+DATA01/PUNITDB/PUNITDB_REDO_2_01.rdo','+DATA01/PUNITDB/PUNITDB_REDO_2_02.rdo') SIZE 50M
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
MAXDATAFILES 254
MAXINSTANCES 8

5.start the instance in nomount and execute above script.(comment out cluster parameters).

SQL>startup nomount pfile='initPUNITDB1.ora'

SQL>createdb.sql

Database Created .

SQL> Select name,open_mode from v$database;

NAME            OPEN_MODE

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

PUNIT             READ WRITE

6.Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:


1.catalog.sql
2.catproc.sql
3.catclust.sql (Run catclust.sql. This command creates the dictionary views needed for Oracle RAC databases).
4.pupbld.sql (system user).

7.Create undo tablespace and thread for second instance.

create UNDO TABLESPACE "UNDOTBS2" DATAFILE '+DATA01/PUNITDB/PUNITDB_undo12_01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 5M  MAXSIZE UNLIMITED;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 ('+DATA01/PUNITDB/PUNITDB_REDO_3_01.rdo','+DATA01/PUNITDB/PUNITDB_REDO_3_02.rdo') SIZE 50M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ('+DATA01/PUNITDB/PUNITDB_REDO_4_01.rdo','+DATA01/PUNITDB/PUNITDB_REDO_4_02.rdo') SIZE 50M;

alter database enable public thread 2;

8.Now uncomment the cluster database parameters and shut down and start the instance on node1.

SQL> startup pfile='initPUNITDB1.ora'

9.now copy the parameter file and password file to other node.And change names according as instance.

 scp initPUNITDB1.ora orapwPUNITDB1 oradb@node2:/u01/oradb/db2/dbs

10.start the second instance.

SQL> startup pfile='initPUNITDB2.ora'

ORACLE instance started

Database Opened.

Database Mounted.

10.Execute the cluster script in any of the node.

SQL>catclust.sql

11.Check the both instances status.

SQL> select instance_number,instance_name,status from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME    STATUS

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

              1 PUNITDB1        OPEN

              2 PUNITDB2        OPEN

12.add the database info to the cluster .

srvctl add database -d B -o /u01/oradb/db2 

srvctl add instance -d PUNITDB -i PUNITDB1 -n node1

srvctl add instance -d PUNITDB -i PUNITDB2 -n node2

srvctl start instance -d PUNITDB -i PUNITDB1

srvctl start instance -d PUNITDB -i PUNITDB2

RMAN COMMANDS

Useful RMAN commands

followed very good article by

http://rd-oracledba.blogspot.com

This article about various RMAN commands which are useful in our daily job.

RMAN will take database backup in two methods

 1.backup set and 2.image copy.

By default method is backup set.

Diff between backup set and image copy:

==============================

A backup set consists of one or more backup pieces, which are physical files written in a format that only RMAN can access.You can also take compressed backups using backup set.

Image copy is a bit-for-bit copy of a database file created on disk. Image copies are identical to copies created with operating system commands like cp on Linux or COPY on Windows, but are recorded in the RMAN repository and so are usable by RMAN.

Monitor RMAN Backups:

==================

set pages 2000 lines 200

COL STATUS FORMAT a9

col START_TIME for a20

col END_TIME for a20

COL hrs FORMAT 999.99

select INPUT_TYPE,

STATUS,

TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,

TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,

ELAPSED_SECONDS/3600 hrs,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

OUTPUT_DEVICE_TYPE

FROM V$RMAN_BACKUP_JOB_DETAILS

order by SESSION_KEY;

To check the progress of your current RMAN backup use this script:

 select recid , output_device_type, dbsize_mbytes, input_bytes/1024/1024/1024 input_gbytes
, output_bytes/1024/1024/1024 output_gbytes , (output_bytes/input_bytes*100) compression
, (mbytes_processed/dbsize_mbytes*100) complete
 , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
from v$rman_status rs , (select sum(bytes)/1024/1024/1024 dbsize_mbytes from v$datafile) 
where status='RUNNING' and output_device_type is not null

BACKUP SET examples :

===================

* To take full backup using tag:

RMAN> BACKUP AS BACKUPSET TAG 'FULL_DB' DATABASE PLUS ARCHIVELOG;

* To take compressed backups:

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

* To creates a level 1 differential incremental backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

* To creates a level 1 cumulative incremental backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

* To take archivelog backup:

rman> backup archivelog all;

rman> backup filesperset 10 archivelog all;

rman> backup archivelog all delete all input;  

rman> backup archivelog all not backed up 1 times;

rman> backup archivelog all not backed up 1 times tag 'arch_tape_backup';

rman> backup  as compressed backupset filesperset 1 archivelog until time 'sysdate - 2/24';

* To list backups:

rman> list backup summary;

rman> list backup;

rman> list backup of database:

rman> list backuppiece 'db_rameshxsx_vgdffj_01';

rman> list backupset 13202;

rman> list backup of datafile 10;

rman> list backupset of datafile 10;

* To list and delete archive files:

rman> list archivelog all;

rman> list backup of archivelog all;

rman> list archivelog from sequence 1145 until sequence 1250;

rman> list backup of archivelog until time 'sysdate-2';

rman> list backup of archivelog from sequence 78895 until sequence 92566;

rman> delete archivelog all;

rman> delete archivelog until time 'sysdate -2';

rman> delete noprompt archivelog until time 'sysdate -3/24';

rman> delete archivelog all completed before 'sysdate-5';

rman> delete noprompt archivelog all  backed up 1 times to device type sbt;

rman> delete noprompt archivelog until time 'sysdate-2' backed up 1 times to device type disk;

rman> delete noprompt archivelog until time 'sysdate - 2/24' backed up 1 times to device type disk;

Note: All delete all input means RMAN will delete archive logs from archive destinations once they are backed up.

Image copy examples:

===================

* To take full image backup with archivelogs

RMAN> BACKUP AS COPY TAG 'FULL_DB' DATABASE PLUS ARCHIVELOG; // with archive logs

RMAN> BACKUP AS COPY INCREMENTAL LEVEL 0 TAG  'FULL_DB'  DATABASE;

To take full image backup to specific location:

rman> backup as copy database tag 'full_db' format '/u02/backup/%b';

rman> backup as copy database tag 'full_db' format '/u01/backup/%f_%b';

(or)

allocate channel ch1 device type disk format '/u02/backup/%b';

backup as copy database tag 'full_db';

release channel ch1;

* To take incremental for image backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'FULL_DB' DATABASE;

* Rollforward full image backup with incremental:

RMAN> RECOVER COPY OF DATABASE WITH TAG 'FULL_DB';

or 

RMAN> RECOVER COPY OF DATABASE WITH TAG 'FULL_DB' UNTIL TIME 'SYSDATE-1';

*  Back Up Controlfile  as copy:

RMAN>BACKUP AS COPY CURRENT CONTROLFILE;

* Backup recovery area to tape:

RMAN> BACKUP RECOVERY AREA TAG 'FULL_DB';

* To take archivelog copy backup

RMAN> BACKUP AS COPY ARCHIVELOG;

* To delete archivelogs copy backup:

RMAN> DELETE COPY OF ARCHIVELOG;

* To list copy backups:

RMAN> LIST COPY OF BACKUP;

RMAN> LIST COPY OF ARCHIVELOG;

RMAN> LIST COPY OF ARCHIVELOG FROM SEQUENCE 10;

RMAN> List Copy of archivelog from sequence 10 until sequence 20;

RMAN> LIST COPY;

* Delete copy backups:

RMAN> delete copy of database tag='REFRESH_DB_8th_DEC';

RMAN> delete copy TAG='REFRESH_DB_8th_DEC';

Crosscheck and Expired:

=====================

Crosschecks update RMAN repository information about backups whose repository records do not match their physical status.

For example, if a user removes archived logs from disk with an os commands (ex: rm) , the repository still indicates that the logs are on disk, when in fact logs are not there .So by using crosscheck command we are checking physical existance of files and if files are not there then rman will mark these missing/deleted files as expired.

rman> crosscheck backup;

rman> crosscheck backup of database;

rman> crosscheck backupset;

rman> crosscheck copy;

rman> crosscheck archivelog all ;

rman> list expired;

rman> list expired archivelog all;

rman> list expired backup;

rman> list expired backup of archivelog all;

rman> delete expired backup;

rman> delete expired archivelog all;

rman> delete force noprompt expired copy tag='REFRESH_DB_8th_DEC';

Obsolete backups:

================

Backups will become obsolete based on retention policy.For example if retention policy is "recovery window 7 days" then rman will mark backups as an obsolete

after 7 days.

RMAN> REPORT OBSOLETE;

RMAN> DELETE OBSOLETE;

RMAN> DELETE FORCE OBSOLETE;

validate :

==========

You can use validate cmd to check database files and archived redo log files are physically and logically  corrupted.

RMAN> BACKUP VALIDATE;

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

RMAN> VALIDATE BACKUPSET 3;

RMAN> VALIDATE DATAFILE 5;

RMAN> VALIDATE DATAFILECOPY ALL;

Catalog files :

==============

Add information about file copies and user-managed backups to the RMAN repository.

CATALOG START WITH '/u02/backup/';

CATALOG START WITH '+FRA_DG';

CATALOG BACKUPPIECE '/u02/backup/annnf0_full1_0.276.895785483';

CATALOG DATAFILECOPY '/u02/backup/users01.dbf' LEVEL 0;

CATALOG ARCHIVELOG '/u02/backup/thread_1_seq_8.321.895785769', '/u02/backup/thread_2_seq_2.326.895781619';

Change :

======

Change command will update the availability status of backups and copies recorded in the RMAN repository.For example if you don't want any particular backup then we can mark them as unavailable.

CHANGE BACKUPSET 4 UNAVAILABLE;

CHANGE ARCHIVELOG ALL UNCATALOG; // for suppose if we moved archive files from one location to another location...then we can uncatlog those files by using change cmd.

Configuration settings :

=================

you can change rman configuration settings and below is one of the example to change the retention policy.

rman> show all;

rman> configure retention policy to redundancy 3;

rman> configure retention policy to recovery window of 7 days;

rman> configure retention policy clear;   // to set to default 

Backup controlfile to trace :

======================

RMAN> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

RMAN> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/U02/BACKUP/CONTROLFILE.CTL';

Control File and Server Parameter File Autobackups:

=======================================

If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current sp file at the end of a successful BACKUP command.

RMAN> show CONTROLFILE AUTOBACKUP;

RMAN configuration parameters for database with db_unique_name RAMESH1 are:

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

To run the rman script:

=======================

rman target / @rman_backup.cmd log=rman_backup.log

rman_backup.cmd

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

run

{ 

allocate channel ch1 device type disk FORMAT '/u02/backup/%b';

BACKUP AS COPY DATABASE TAG 'FULL_DB';

release channel ch1;

}

Trace a rman session:

===============

rman target / debug trace rman_ramesh1_crosscheck.trc log rman_ramesh1_crosscheck.log

RUN

{

ALLOCATE CHANNEL C1 DEVICE TYPE DISK;

ALLOCATE CHANNEL C2 DEVICE TYPE SBT;

ALLOCATE CHANNEL C3 DEVICE TYPE DISK;

ALLOCATE CHANNEL C4 DEVICE TYPE SBT;

CROSSCHECK COPY;

}

control_file_record_keep_time parameter value change:

========================================

select name, ISSYS_MODIFIABLE from v$parameter where name='control_file_record_keep_time';

alter system set control_file_record_keep_time=10 scope=both sid='*';