Monday, March 30, 2009

Move controlfiles from the Filesystem to ASM

1. Identify the location of the current controlfile:

SQL> select name from v$controfile;
NAME
——————————————————————————–
/opt/app/oracle/product/11/db_1/dbs/standby_control.ctl’

2. Shutdown the database and start the instance:
SQL> shutdown normal
SQL> startup nomount

3. Use RMAN to move the controlfile to ASM :

rman target /

RMAN> restore controlfile to ‘+DATA’ from ‘/opt/app/oracle/product/11/db_1/dbs/standby_control.ctl’;

Starting restore at 30-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=539 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 30-MAR-09

RMAN> exit

We are only specifying the name of the diskgroup, so Oracle will create an OMF (Oracle Managed File).
Use ASMCMD or sqlplus to identify the name assigned to the controlfile

4. On the ASM instance, identify the name of the controlfile:

Using ASMCMD:
$ asmcmd
ASMCMD> pwd
+data/standby/controlfile
ASMCMD> ls
current.1384.681888623
current.1385.681888685

This is just the name assigned for the identical copy of the current controlfile.

5. On the database side:

* Modify init.ora or spfile, replacing the new path to the init parameter control_files.
* if using init.ora, just modify the control_files parameter and restart the database.

6. start the instance.

Verify that new control file has been recognized. If the new controlfile was not used, the complete procedure needs to be repeated.

7. SQL> show parameter control_file

NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string +DATA/standby/controlfile/curr
ent.1384.681888623, +DATA/standby/controlfile/current.1385.681888685

NOTE : Steps 3 to 5 Could be repeated to multiplex the contolfiles to more than one on each diskgroup .

Sunday, March 29, 2009

Copy ASM Files with DBMS_FILE_TRANSFER

You can copy ASM Files using DBMS_FILE_TRANSFER pl/sql package. Lets see how to copy an archive log file from ASM storage to local disk.

RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile

RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=129 instance=rdba1 devtype=DISKvalidation succeeded for archived logarchive log filename=+FRA/rdba/archivelog/2009_03_29/thread_1_seq_2197.485.682769833 recid=490 stamp=682769833Crosschecked 1 objects

RMAN> exit
Recovery Manager complete.

C:\>sqlplus / as sysdba

SQL> create directory XXARCsrc as '+FRA/rdba/archivelog/2009_03_29';
Directory created.

SQL> create directory XXARCdest as 'C:\';
Directory created.

SQL> grant read, write on directory XXARCsrc to public;
Grant succeeded.

SQL> grant read,write on directory XXARCdest to public;
Grant succeeded.

SQL> execute dbms_file_transfer.copy_file('XXARCsrc','thread_1_seq_2197.485.682769833','XXARCdest','arc2197');

Now look for the archive file arc2197 in local disk in c-drive.

Note: This way you can copy any file from ASM to the disk.

To view the files on ASM use the ASMCMD tool,

SET oracle_home=C:\oracle\product\10.2.0\asm_1
SET oracle_sid=+ASM1

C:\>asmcmd
ASMCMD> ls
/BKP
/DATA
/FRA

ASMCMD> cd data
ASMCMD> ls
RDBA/

ASMCMD> cd rdba

C:\>set oracle_home=C:\oracle\product\10.2.0\db_1

C:\>set oracle_sid=+ASM1

C:\>asmcmd
ASMCMD> ls
BKP/
DATA/
FRA/

ASMCMD> cd data

ASMCMD> ls
DB_UNKNOWN/
RDBA/

ASMCMD> cd db_unknown
ASMCMD> ls
PARAMETERFILE/

ASMCMD> cd parameterfile

ASMCMD> ls
SPFILE.268.665669211

ASMCMD> cd ..
ASMCMD> cd ..
ASMCMD> cd rdba

ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
spfilemol.ora

ASMCMD> cd datafile

ASMCMD> ls
ARCHDATA.279.667315727
AUTOSCAN.278.667315457
COMPANY.275.667315065
COMPANY_IDX.273.667314429
EMPLOYEE.271.667314047
EMP_IDX.284.667316721
EXAMPLE.264.663930173
INDX.290.667317683
INDX_MOL1.269.667313539
INDX_MOL2.281.667316185
MAIN_MOL.277.667315275
MAIN_MOL.282.667316309
MOL_DNRD.289.667317613
MOL_DNRD_INDEX.288.667317545
OTHERS.280.667315911
OTHER_IDX.276.667315173
SYSAUX.257.663930059
SYSTEM.256.663930057
TEMPORARAY.287.667317499
TNT.272.667314321
TNT_IDX.274.667314579
TNT_IDX1.291.667317753
TOOLS.285.667317351
TUNE_INDEX.270.667313981
UNDOTBS1.258.663930059
UNDOTBS2.265.663930307
USERS.259.663930059
USR.286.667317429
VISA_CANCEL_CHNG.283.667316489

Monday, March 16, 2009

Apps 11i Technical Interview Question/Answers

How to know if a specific patch is installed or not?
SELECT * FROM AD_BUGS WHERE BUG_NUMBER=x;
Here substitute x with patch number, and if the query returns a value means the patch is already applied.

What is US directory in $AD_TOP or under various product TOP’s . US directory is defauly language directory in Oracle Applications. If you have multiple languages Installed in your Applications then you will see other languages directories besides US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.

What are main concurrent Manager types.
ICM - Internal Concurrent Manager which manages concurrent Managers
Standard Managers - Which Manage processesing of requests.
CRM - Conflict Resolution Managers , resolve conflicts in case of incompatibility.

What is difference between ICM, Standard Managers & CRM in Concurrent Manager?
ICM stand for Internal Concurrent Manager, which controls other managers. If it finds other managers down , it checks & try to restart them. You can say it as administrator to other concurrent managers. It has other tasks as well.
Standard Manager These are normal managers which control/action on the requests & does batch or single request processing.
CRM acronym for Conflict Resolution Manager is used to resolve conflicts between managers & request. If a request is submitted whose execution is clashing or it is defined not to run while a particular type of request is running then such requests are actioned/assigned to CRM for Incompatibilities & Conflict resolution.

Where is Concurrent Manager log file location.
By default standard location is $APPLCSF/$APPLLOG, in some cases it can go to $FND_TOP/log as well

What is multi node system?
Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database; Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.
ICM stand for Internal Concurrent Manager, which controls other managers. If it finds other managers down , it checks & try to restart them. You can say it as administrator to other concurrent managers. It has other tasks as well.
Standard Manager These are normal managers which control/action on the requests & does batch or single request processing.
CRM acronym for Conflict Resolution Manager is used to resolve conflicts between managers & request. If a request is submitted whose execution is clashing or it is defined not to run while a particular type of request is running then such requests are actioned/assigned to CRM for Incompatibilities & Conflict resolution.

Where is Concurrent Manager log file location.
By default standard location is $APPLCSF/$APPLLOG, in some cases it can go to $FND_TOP/log as well

What is multi node system?
Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database; Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.

What is .dbc file, where its stored, what use of .dbc file?
dbc as name says is database connect descriptor file which stores database connection information used by application tier to connect to database. This file is in directory $FND_TOP/secure also called as FND_SECURE

How to determine Oracle Apps 11i Version?
Select RELEASE_NAME from fnd_product_groups;
You should see output like
RELEASE_NAME
———————–
11.5.9 or 11.5.10.2

Whats is TWO_TASK in Oracle Database?
TWO_TASK mocks your tns alias which you are going to use to connect to database. Lets assume you have database client with tns alias defined as PROD to connect to Database PROD on machine teachmeoracle.com listening on port 1521. Then usual way to connect is sqlplus username/passwd@PROD ; now if you don’t want to use @PROD then you set TWO_TASK=PROD and then can simply use sqlplus username/passwd then sql will check that it has to connect to tnsalias define by value PROD i.e. TWO_TASK

What is GWYUID ?
GWYUID , stands for Gateway User ID and password. Usually like APPLSYSPUB/PUB
Where GWYUID defined & what is its used in Oracle Applications?
GWYUID is defined in dbc i.e. Database Connect Descriptor file. It is used to connect to database by think clients

How to check number of forms users at any time?
Forms Connections initiate f60webmx connections so you can use
ps -ef grep f60webmx wc -l

What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD?
0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where
0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.
‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

If your system has more than one Jinitiator, how will the system know, which one to pick. ?
When client makes a forms connection in Oracle Applications, forms client session uses configuration file defined by environment variable FORMS60_WEB_CONFIG_FILE also called as appsweb config file. These days this file is of format appsweb_$CONTEXT.cfg The initiator version number defined by parameter jinit_ver_name in this file will be used.

What are .ldt & .lct files which you see in apps patch or with FNDLOAD?
ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.

What is dev60cgi & ifcgi60?
cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/ifcgi60

What is difference between COMPILE_ALL=SPECIAL and COMPILE=ALL while compiling Forms?
Both the options will compile all the PL/SQL in the resultant .FMX, .PLX, or .MMX file but COMPILE_ALL=YES also changes the cached version in the source .FMB, .PLL, or .MMB file. This confuses version control and build tools (CVS, Subversion, make, scons); they believe you’ve made significant changes to the source. COMPILE_ALL=SPECIAL does not do this.

What is ps -ef or ps command in Unix ?
ps is unix/linux utility or executable to find status of process. Used mainly to find if services/process is running or not.

Monday, March 9, 2009

Oracle Clusterware - Voting Disk

What is a voting disk?
A voting disk is a file that manages information about node membership.

What are the administrative tasks involved with voting disk?
Following administrative tasks are performed with the voting disk :
1) Backing up voting disks
2) Recovering Voting disks
3) Adding voting disks
4) Deleting voting disks
5) Moving voting disks

How do we backup voting disks?
1) Oracle recommends that you back up your voting disk after the initial cluster creation and after we complete any node addition or deletion procedures.
2) First, as root user, stop Oracle Clusterware (with the crsctl stop crs command) on all nodes. Then, determine the current voting disk by issuing the following command:
crsctl query votedisk css
3) Then, issue the dd or ocopy command to back up a voting disk, as appropriate.

Give the syntax of backing up voting disks:-
On Linux or UNIX systems:
dd if=voting_disk_name of=backup_file_name
where,
voting_disk_name is the name of the active voting disk
backup_file_name is the name of the file to which we want to back up the voting disk contents
On Windows systems, use the ocopy command:
ocopy voting_disk_name backup_file_name

What is the Oracle Recommendation for backing up voting disk?
Oracle recommends us to use the dd command to backup the voting disk with aminimum block size of 4KB.

How do you restore a voting disk?
To restore the backup of your voting disk, issue the dd or ocopy command for Linux and UNIX systems or ocopy for Windows systems respectively.
On Linux or UNIX systems:
dd if=backup_file_name of=voting_disk_name
On Windows systems, use the ocopy command:
ocopy backup_file_name voting_disk_name
where,
backup_file_name is the name of the voting disk backup file
voting_disk_name is the name of the active voting disk

How can we add and remove multiple voting disks?
If we have multiple voting disks, then we can remove the voting disks and add them back into our environment using the following commands, where path is the complete path of the location where the voting disk resides:
crsctl delete css votedisk path
crsctl add css votedisk path

How do we stop Oracle Clusterware?When do we stop it?
Before making any modification to the voting disk, as root user, stop Oracle Clusterware using the crsctl stop crs command on all nodes.

How do we add voting disk?
To add a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to add:
crsctl add css votedisk path -force

How do we move voting disks?
To move a voting disk, issue the following commands as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to move:
crsctl delete css votedisk path -force
crsctl add css votedisk path -force

How do we remove voting disks?
To remove a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to remove:
crsctl delete css votedisk path -force

What should we do after modifying voting disks?
After modifying the voting disk, restart Oracle Clusterware using the crsctl start crs command on all nodes, and verify the voting disk location using the following command:
crsctl query css votedisk

When can we use -force option?
If our cluster is down, then we can include the -force option to modify the voting disk configuration, without interacting with active Oracle Clusterware daemons. However, using the -force option while any cluster node is active may corrupt our configuration.