ORACLE DBA: Oracle Apps

When the file is created using the utl_file plsql package, how to set the desired file permission ?

Solution
In the Client/Server architecture , if you connect to Oracle database from any client workstation, through SQLNET, a session is created on behalf of your user process. It is the responsibility of the listener process to connect user process with available shared server / dispatcher process. These are the background process serving on behalf of user process. Normally oracle is the owner of these background process.

The file permissions is not a function of the UTL_FILE package at all.The concept is if you are establishing a local session on the server,then the shadow process you started inherits the permissions as you logged in as..
However if you login from a remote client say a SQLplus session from a NT workstation, then the file inherits the permission of the TNS Listener of the server. The TNS Listener permission is that permission as at the time when the listener was started by the oracle user. By stopping the listener and logging back in and restarting listener after setting the new umask value, the <oracle> user basically inherits the new umask value which in turn passes it to the new listener.Hence whenever you change the umask value , you should always restart the listener so that it gets the umask value of the user who has started the listener.

1. Login to server as oracle
2. stop the listener
3. set the desired umask value
4. disconnect remote session (session from where you are initiating procedure)
5. log back into the server as oracle to inherit new umask value
6. As a check - type 'umask' at the prompt to check the value
7. start the listener
8. from remote client start a new sqlplus session to test procedure.

You must get the right permissions this time.

You must be connected to database using listener or bequeath connection. So in both the cases the UMASK should be specified properly.

If the database is started using user oracle (UMASK=022) and listener started using user test1(UMASK=002). Always the new files will be created with the ownership of oracle. Hence oracle should have proper permissions to access the folder/directory where file is created.

If you are connected to database using bequeath connection (sqlplus scott/tiger), then file permissions will be inherited from user oracle. Hence the file permissions would be (rw-r-r). If you have connected through listener than file permissions will be inherited from user test1 because the listener was started by test1. Hence the file permissions would be (rw-rw-r).

If the file is already existing and you are trying to change the permission using utl_file package by setting new umask value, the changes will not be affected. You will have to manually change the permission using 'chmod' command.

Example :

Server :

=========

1. Login to server as oracle
login as: oracle
oracle@10.177.59.119's password:
Last login: Thu Oct 18 07:50:27 2007 from 10.177.59.103

2. stop the listener
[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 18-OCT-2007 07:53:47

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

3. set the desired umask value
[oracle@localhost ~]$ umask
0022
[oracle@localhost ~]$ umask 002

4. disconnect remote session (session from where you are initiating procedure)
5. log back into the server as oracle to inherit new umask value
6. As a check - type 'umask' at the prompt to check the value
7. start the listener
[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 18-OCT-2007 07:54:12

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

Starting /home/databases/ORA10203/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /home/databases/ORA10203/network/admin/listener.ora
Log messages written to /home/databases/ORA10203/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 18-OCT-2007 07:54:12
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/databases/ORA10203/network/admin/listener.ora
Listener Log File /home/databases/ORA10203/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "ORA10203" has 1 instance(s).
Instance "ORA10203", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost ~]$

Client :
=========

8. from remote client start a new sqlplus session to test procedure.

C:\Documents and Settings\aldsouza>sqlplus scott/tiger@ORA10201_LINUX

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 18 07:47:20 2007

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> declare
2
3 f1 utl_file.file_type;
4
5 begin
6
7 f1 := utl_file.fopen('TEST_DIR','test.txt','w');
8 utl_file.fclose(f1);
9
10 end;
11 /

PL/SQL procedure successfully completed.

SQL>

The file will be created in the TEST_DIR with the following permissions

-rw-rw-r-- 1 oracle dba 0 Oct 18 08:00 test.txt

RAC to RAC DB Tier cloning

1)OS/DB specifications

OS version is Oracle Solaris 10 and database version is 11.1.0.7, Apps - 12.1.3 Cloning from ERPDEV to ERPPAT

Source - ERPDEV on server0091/93

Target - ERPPAT on server0090/92

Prepare the source system database tier for cloning (at ERPDEV)

Log on to the source system as the ORACLE user, and run the following commands:

$ cd [RDBMS ORACLE_HOME]/appsutil/scripts/[CONTEXT_NAME]
$ perl adpreclone.pl dbTier

Configure Primary Node :

2) Copy RDBMS Home from Source to Target

{server0091:oracle:}: /home/oracle/scripts =>cat cp_db.sh

echo "Copy of RDBMS started at `date`"

cd /u01/app/oracle/product/11.0.1

tar cef - ./* | gzip > /erpdev/orashr/backup/db/db.tar.gz

echo "Copy of RDBMS finished at `date`"

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat =>gunzip db.tar.gs

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat =>tar -xvf db.tar

3) Backup ASM_HOME/network/admin directory in target.

4) Create pairsfile.txt File for Primary Target Node

cd /u01/app/oracle/product/11.0.1_erppat/appsutil/clone and create pairsfile.txt

Set the following values :

s_undo_tablespace=[UNDOTBS1 for Initial Node]

s_dbClusterInst=[Total number of Instances in a cluster e.g. 2]

s_db_oh=[Location of new ORACLE_HOME]

Sample of pairsfile.txt

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat/appsutil/clone =>cat pairsfile.txt

s_undo_tablespace=APPS_UNDOTS1

s_dbClusterInst=2

s_db_oh=/u01/app/oracle/product/11.0.1_erppat

5) Create Context File for Target Primary Node

perl adclonectx.pl \

contextfile=[PATH to OLD Source RAC contextfile.xml] \

template=[NEW ORACLE_HOME]/appsutil/template/adxdbctx.tmp \

pairsfile=[NEW ORACLE_HOME]/appsutil/clone/pairsfile.txt \

initialnode

Sample value and inputs --

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin =>perl adclonectx.pl \

> contextfile=/u01/app/oracle/product/11.0.1_erppat/appsutil/ERPDEV1_server0091.xml \

> template=/u01/app/oracle/product/11.0.1_erppat/appsutil/template/adxdbctx.tmp \

> pairsfile=/u01/app/oracle/product/11.0.1_erppat/appsutil/clone/pairsfile.txt \

> initialnode

Redwood Shores, California, USA

Oracle Applications Rapid Clone

Version 12.0.0

adclonectx Version 120.23.12010000.1

Running:

/u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/../jre/bin/java -Xmx600M -classpath /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/../jlib/ojdbc5.jar:/u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/../jlib/xmlparserv2.jar:/u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/../jlib/java oracle.apps.ad.context.CloneContext -e /u01/app/oracle/product/11.0.1_erppat/appsutil/ERPDEV1_server0091.xml -tmpl /u01/app/oracle/product/11.0.1_erppat/appsutil/template/adxdbctx.tmp -pairsfile /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/pairsfile.txt -initialnode

Enter the APPS password : erpdevapps

Log file located at /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/CloneContext_1028139319.log

Provide the values required for creation of the new Database Context file.

Target System Hostname (virtual or normal) [server0090] :

It is recommended that your inputs are validated by the program.

However you might choose not to validate them under following circumstances:

-If cloning a context on source system for a remote system.

-If cloning a context on a machine where the ports are taken and

you do not want to shutdown the services at this point.

-If cloning a context but the database it needs to connect is not available.

Do you want the inputs to be validated (y/n) [n] ? :

Target Instance is RAC (y/n) [y] :

Target System Database Name : ERPPAT

Target System Port Pool [0-99] : 1

Report file located at /u01/app/oracle/product/11.0.1_erppat/appsutil/out/portpool.lst

Complete port information available at /u01/app/oracle/product/11.0.1_erppat/appsutil/out/portpool.lst

Provide information for the initial RAC node:

Host name [server0090] :

Virtual Host name [null] : server0090-vip

Instance number [1] :

Private interconnect name [server0090] : 192.168.114.23

Target System Base Directory : /u01/app/oracle/product/11.0.1_erppat

Oracle OS User [oracle] :

Oracle OS Group [oinstall] :

Target System utl_file_dir Directory List : /usr/tmp

Number of DATA_TOP's on the Target System [2] : 1

Target System DATA_TOP Directory 1 : +ERPPAT

Do you want to preserve the Display [localhost:12.0] (y/n) ? : n

Target System Display [server0090:0.0] :

New context path and file name [/u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml] :

Do you want to overwrite it (y/n) [n] ? : y

Replacing /u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml file.

Creating the new Database Context file from :

/u01/app/oracle/product/11.0.1_erppat/appsutil/template/adxdbctx.tmp

The new database context file has been created :

/u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml

Log file located at /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/CloneContext_1028139319.log

contextfile=/u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml

Check Clone Context logfile /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin/CloneContext_1028139319.log for details.

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin =>

Note: If the most current AutoConfig Template patch as listed in OracleMetalink Note 387859.1 has already been applied to the source system, it is necessary to edit the value of the target system context variable "s_db_listener" to reflect the desired name for the tns listener. The traditionally accepted value is "LISTENER_[HOSTNAME]" but may be any valid value unique to the target host.

Also check and change local_listener and remote_listener value at context file.

6) Detach Existing Oracle Home at Primary Home

./runInstaller -detachHome ORACLE_HOME=/u01/app/oracle/product/11.0.1_erppat

7) Run dbTeckStack at primary node.

perl adclone.pl \

java=<ORACLE_HOME>/ appsutil/clone/jre \

component=dbTechStack \

mode=apply \

stage=OH/appsutil/clone \

method=CUSTOM \

dbctxtg=<ORACLE_HOME>/appsutil/<DB Context File> \

showProgress contextValidated=true

Following is the sample command :

{server0090:oracle:}: /u01/app/oracle/product/11.0.1_erppat/appsutil/clone/bin =>perl adclone.pl \

> java=/u01/app/oracle/product/11.0.1_erppat/appsutil/clone/jre \

> component=dbTechStack \

> mode=apply \

> stage=/u01/app/oracle/product/11.0.1_erppat/appsutil/clone \

> method=CUSTOM \

> dbctxtg=/u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml \

> showProgress contextValidated=true

Logfile Path -- /u01/app/oracle/product/11.0.1_erppat/appsutil/log/ERPPAT1_server0090/ ApplyDBTechStack_11031815.log

And corresponding OH clone path -- /u01/app/oraInventory/logs/cloneActions2010-11-03_06-15-90PM.log

/u01/app/oracle/product/11.0.1_erppat/appsutil/log/ERPPAT1_server0090/make_11031816.log

/u01/app/oracle/product/11.0.1_erppat/appsutil/log/ERPPAT1_server0090/ohclone.log

8) Create the target database

a) We need to take hot backup from erpdev and save it to local disk using RMAN

run{

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk4 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk5 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk6 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk7 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

ALLOCATE CHANNEL disk8 DEVICE TYPE DISK FORMAT '/erpdev/orashr/bkp/%U';

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

}

b) FTP the backup and archive log files to server0090(ERPPAT) after creating the same directory where the
source db backup files were saved, (alternate is to use ACTIVE DUPLICATION)

c) Create a parameter file for ERPPAT database (auxiliary).

The following parameters need to change.

1) control_files

2) diagnostic_dest

3) sga_target

4) db_create_file_dest (ASM location)

5) db_create_online_log_dest_1 (ASM Location)

6) log_archive_dest (Enabled archive log mode)

7) cluster_database=false

8) _no_recovery_through_resetlogs=TRUE

d) Set ORACLE_HOME and ORACLE_SID accordingly

Create a password file for auxiliary database using below command.

%orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password= xxxxxxxx

e) Start auxiliary database in NO MOUNT state

startup nomount pfile=<path of init.ora>;

g) Create a static listener for auxiliary database and reload, because auxiliary database will not register
itself with the listener.

h) Make sure that backup pieces are on the same location where it were there on production db.
If you don’t have the same location, then make RMAN aware of the changed location.

i) Duplicate Target Database using RMAN Duplicate command

Connect to target and auxiliary
      rman target sys/password_sys@target_db auxiliary sys/password_sys@auxiliary_db
     RMAN> DUPLICATE TARGET DATABASE TO ERPDUP;

Note:-We need to use password file in ERPDEV and also check remote_login_passwordfile=EXCLUSIVE so that we can connect to ERPDEV using sys user through net alias. Please make necessary change in tnsnames.ora

j) Incorporate RAC related change

cluster_database = TRUE in init.ora

and also check the remote_listener and local_listener values are correct

k) Recreate redo logs of thread 2 & Enable second thread and startup the second instance.

ALTER DATABASE ADD LOGFILE THREAD 2

GROUP 3 (‘..../redo03a’,‘…/redo03b’) size 100M,

GROUP 4 (‘..../redo04a’,‘..../redo04b’) size 100M ;

SQL> ALTER DATABASE ENABLE PUBLIC THREAD 2;

SQL> startup;

l) Check redo thread and temp files after duplication of database.

select * from v$log;

SELECT * FROM DBA_TEMP_FILES OR SELECT * FROM V$TEMPFILE

m) Check sqlplus connection with primary SID.

n) Check that Actual IP’s are also included, otherwise add.

e.g --

{server0090:oracle:}: /u01/app/oracle/product/asm/network/admin =>cat listener.ora

# listener.ora.server0090 Network Configuration File: /u01/app/oracle/product/asm/network/admin/listener.ora.server0090

# Generated by Oracle configuration tools.

LISTENER_SERVER0090 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = server0090-vip)(PORT = 1591)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = server0090-vip)(PORT = 1922)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = server0090-vip)(PORT = 1923)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1591)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = server0090.domain.domain1)(PORT = 1922)(IP = FIRST))

)

9)Run the library update script against the RAC database.

$ cd [RDBMS ORACLE_HOME]/appsutil/install/[CONTEXT_NAME]

$ sqlplus "/ as sysdba" @adupdlib.sql so

10)Start RDBMS listener and check sqlplus connection with primary SID.

Shutdown ASM listener and start up (if it was up by CRS) newly created RDBMS listener .

11)Configure the primary target database using DBCONFIG : (check TNS_ADMIN)

$ cd [RDBMS ORACLE_HOME]/appsutil/clone/bin

$ perl adcfgclone.pl dbconfig <ORACLE_HOME>/appsutil/DB Context file

$ perl adcfgclone.pl dbconfig /u01/app/oracle/product/11.0.1_erppat/appsutil/ERPPAT1_server0090.xml

Logfile- /u01/app/oracle/product/11.0.1_erppat/appsutil/log/ERPPAT1_server0090/ ApplyDatabase_10281449.log

Configure Secondary Node :

Uncompress the archived ORACLE_HOME transferred from the Source System

Uncompress the source system ORACLE_HOME archive to a location matching that present on your target system primary node. The directory structure should match that present on the newly created target system primary node.

$ {server0092:oracle:}: /u01/app/oracle/product/11.0.1_erppat =>tar -xvf db.tar.gz

11) Archive the [ORACLE_HOME]/appsutil directory structure from the new Primary Node

$ cd [ORACLE_HOME]

$ zip -r appsutil_node1.zip appsutil

13 ) Copy appsutil_node1.zip to the Secondary Target Node

Transfer and then expand the appsutil_node1.zip into the secondary target RAC node [NEW ORACLE_HOME].

$ cd [NEW ORACLE_HOME]

$ unzip -o appsutil_node1.zip

14 ) Update pairsfile.txt for the Secondary Target Node

Alter the existing pairsfile.txt (from the first target node) and change the s_undo_tablespace parameter. As this is the second node, the correct value would be UNDOTS2. As an example, the [NEW_ORACLE_HOME]/appsutils/clone/pairsfile.txt would look like:

s_undo_tablespace= APPS_UNDOTS2

s_dbClusterInst=2

s_db_oh=/u01/app/oracle/product/11.0.1_erppat

15) Create a Context File for the Secondary Node

Navigate to [NEW_ORACLE_HOME]/appsutil/clone/bin and run the adclonectx.pl utility as follows:

perl adclonectx.pl \

contextfile=[Path to Existing Context File from the First Node] \

template=[NEW ORACLE_HOME]/appsutil/template/adxdbctx.tmp \

pairsfile=[NEW ORACLE_HOME]/appsutil/clone/pairsfile.txt \

addnode

Where:

contextfile : Full path to the existing context file from the first (primary) node.

template : Full path to the existing database context file template.

pairsfile : Full path to the pairsfile created on last step.

Note—Please perform following step before proceed.

./runInstaller -detachHome ORACLE_HOME=/u01/app/oracle/product/11.0.1_erppat

Also check and change local_listener and remore listener value at context file.

16 ) Configure NEW ORACLE_HOME

Run the commands below to move to the correct directory and continue the cloning process: $ cd [NEW ORACLE_HOME]/appsutil/clone/bin

$ perl adcfgclone.pl dbTechStack [Full path to the database context file created in previous step]

check the logfiles in case completion with warnings.

17) Source the new environment file in the ORACLE_HOME

Run the commands below to move to the correct directory and source the environment:

$ cd [NEW ORACLE_HOME]

$ ./[CONTEXT_NAME].env

18) Modify [SID]_APPS_BASE.ora

Edit the [SID]_APPS_BASE.ora file and change the control file parameter to reflect the correct control file location on the shared storage. This will be the same value as in the [SID]_APPS_BASE.ora on the target system primary node which was just created.

19) Start Oracle RAC Database

Start the database using the following commands:

SQL> connect / as sysdba

SQL> startup

20) Execute AutoConfig on both the node. (Point TNS_ADMIN to ASM Home)

Run AutoConfig to generate the proper listener.ora and tnsnames.ora files:

$ cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME

$ ./adautocfg.sh appspass=[APPS Password]

21) Point TNS_ADMIN to ASM HOME/network/admin, shutdown EBS RDBMS listeners and start ASM listeners at both the tiers.

Check that Actual IP’s are also included, otherwise add.