ASM Installation Steps

ASM Installation Steps

ASMLib is a support library for the Automatic Storage Management feature of Oracle Database 10g. Automatic Storage Management (ASM) simplifies database administration. It eliminates the need for the DBA to directly manage potentially thousands of Oracle database files, requiring only the management of groups of disks allocated to the Oracle Database. ASMLib allows an Oracle Database using ASM more efficient and capable access to the disk groups it is using.
Oracle ASMLib Downloads for SuSE Linux Enterprise Server 10

Library and Tools
• oracleasm-support-2.1.3-1.SLE10.x86_64.rpm
• oracleasmlib-2.0.4-1.SLE10.x86_64.rpm
Drivers for kernel 2.6.16.60-0.21
• oracleasm-2.6.16.60-0.21-smp-2.0.4-1.SLE10.x86_64.rpm
• oracleasm-2.6.16.60-0.21-default-2.0.4-1.SLE10.x86_64.rpm
Installing ASMLib
rpm -Uvh oracleasm-support-2.1.3-1.SLE10.x86_64.rpm
rpm -Uvh oracleasmlib-2.0.4-1.SLE10.x86_64.rpm
rpm –Uvh oracleasm-2.6.16.60-0.21-smp-2.0.4-1.SLE10.x86_64.rpm
rpm –Uvh oracleasm-2.6.16.60-0.21-default-2.0.4-1.SLE10.x86_64.rpm


dwprod:~ # rpm -qa|grep oracleasm
oracleasm-2.6.16.60-0.21-smp-2.0.4-1.SLE10
oracleasmlib-2.0.4-1.SLE10
oracleasm-2.6.16.60-0.21-default-2.0.4-1.SLE10
oracleasm-support-2.1.3-1.SLE10
Configuring ASMLib
/etc/init.d/oracleasm configure
It will ask for the user and group that default to owning the ASM driver access point. If the database was running as the 'oracle' user and the 'dba' group, the output would look like this:


[root@ca-test1 /]# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration [ OK ]
Creating /dev/oracleasm mount point [ OK ]
Loading module "oracleasm" [ OK ]
Mounting ASMlib driver filesystem [ OK ]
Scanning system for ASM disks [ OK ]

[root@ca-test1 /]# /etc/init.d/oracleasm enable
Making Disks Available to ASMLib
/etc/init.d/oracleasm createdisk VOL1 /dev/sdc1
Every disk that ASMLib is going to be accessing needs to be made available. This is accomplished by creating an ASM disk. The /etc/init.d/oracleasm script is again used for this task:
[root@ca-test1 /]# /etc/init.d/oracleasm createdisk VOL1 /dev/sdg1
Creating Oracle ASM disk "VOL1" [ OK ]


[root@ca-test1 /]# /etc/init.d/oracleasm listdisks


[root@ca-test1 /]# /etc/init.d/oracleasm querydisks


Creating ASM instance and Diskgroup.
A. Configuration of ASM instance parameter with the below contents and respective directory structure.
*.asm_diskgroups='ORADATA1'
+ASM.asm_diskgroups='ORADATA1'#Manual Mount
*.asm_diskstring='ORCL:*'
*.background_dump_dest='/opt/oracle/admin/+ASM/bdump'
*.core_dump_dest='/opt/oracle/admin/+ASM/cdump'
*.db_unique_name='+ASM'
*.instance_name='+ASM'
*.instance_type='asm'
*.large_pool_size=12M
*.processes=70
*.remote_login_passwordfile='SHARED'
*.user_dump_dest='/opt/oracle/admin/+ASM/udump'
*._asm_wait_time=SB4MAXVAL
B. Bringing up the ASM instance in to nomount state to create the diskgroup.
$export ORACLE_SID=+ASM
$export ORACLE_HOME=
$sqlplus “/as sysdba”
sql>startup nomount
sql>create diskgroup ‘ORADATA1’ external redundancy disk ‘ORCL: VOL1’ size 1126409m;
The above command will create the diskgroup with dismount state.
We can view it from v$asm_diskgroup.
sql> alter diskgroup ORADATA1 mount;-----Ready to use by the RDBMS client.

References :

http://blogs.oracle.com/simonthorpe/2009/06/configuring_oracle_asm_disks_i.html

http://babudba.blogspot.com/2009/03/marking-disk-vol1-as-asm-disk-failed.html

-Shailesh

cross platform (AIX to HP) single instance database to RAC using RCONFIG tool

cross platform (AIX to HP) single instance database to RAC using RCONFIG tool

converting a cross platform (AIX to HP) single instance database to 2 node RAC using the RCONFIG tool.

If you were following my blog, you might aware that we have a plan of implementing 16 node production RAC on HP UX Superdom Itanium servers using EMC DMX4 SAN storage.

As part of production implementation, last week, I have configured initial 8 node RAC successfully and the immediate plans were to migrate the databases across operating systems (AIX to HP ux) and convert a single instance database to RAC. I have done this exercise on the development setup sucessfully, the only change this time is that I have used RCONFIG utility to convert single instance database to RAC. Earlier I have used DBCA method to do the same.

Oracle provides following methods to convert a single instance database to RAC:

Grid Control

DBCA

Manual

RCONFIG(from 10gR2)

I must say, each DBA may choose a different method according to his/her convineance to achieve the result. The following illustrates how I have migrated a database from AIX to HP UX and from a single instance to RAC (on 2 node).

Database and OS details

Source:

Database 10gR2 (10.2.0.4)

OS : AIX

non-ASM

Target:

Databae 10gR2 (10.2.0.4)

OS : HP UX Superdom Itaninum

ASM

RCONFIG brief

RCONFIG tool is introuced in Oracle 10gR2 and the main functionality of this tool is to convert single instance database to RAC.

The tool(rconfig) can be found under $ORACLE_HOME/bin directry.

The xml input file (ConvertToRAC.xml) resides under $ORACLE_HOME/assistants/rconfig/sampleXMLs (it is recommended to copy the file before using it).

Log files(rconfig.log and etc) can be found under $ORACLE_HOME/cfgtoollogs/rconfig

The Convert verify option in the ConvertToRAC.xml file has three options:

• Convert verify="YES": rconfig performs checks to ensure that the prerequisites for single-instance to RAC conversion have been met before it starts conversion
• Convert verify="NO": rconfig does not perform prerequisite checks, and starts conversion
• Convert verify="ONLY" : rconfig only performs prerequisite checks; it does not start conversion after completing prerequisite checks

Before you actually starts the convert process, you can run the rconfig with convert verify=ONLY option which basically check all the prerequisites. At the end, the result code either would be 0 or 1. Where 0 stands for operation success and 1 stand for operation failures and in case of failure, it will also gives you the reason of failure.

Following RMAN command converts the database from AIX to HP UX platform.

RMAN> convert database new database 'ORCL'

transport script '/tmp_backup/tempdb/transport.sql'

to platform 'HP-UX IA (64-bit)'

db_file_name_convert

'+DG_ORCL/ORCL/datafile','/tmp_backup/tempdb';

Move all the converted datafiles to target machine and follow the ML NOTE: 414878.1 for further details on how to start the on target machine.

Following illustrate how to convert single instance database to RAC using the RCONFIG tool:

cp $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC.xml convertdb.xml

modify the convertdb.xml file according to your environment. Following is the example:

==

convert_t24uat.xml 46 lines, 2964 characters

?xml version=1.0 encoding=UTF-8?

RConfig xmlns:n=http://www.oracle.com/rconfig

xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance

xsi:schemaLocation=http://www.oracle.com/rconfig

ConvertToRAC

Verify does a precheck to ensure all pre-requisites are met, before the conversion is attempted. Allowable values are: YES|NO|O

NLY

Convert verify=NO

Specify current OracleHome of non-rac database for SourceDBHome

SourceDBHome /u00/app/oracle/product/10.2.0/db_1 SourceDBHome

Specify OracleHome where the rac database should be configured. It can be same as SourceDBHome

TargetDBHome/u00/app/oracle/product/10.2.0/db_1 TargetDBHome

Specify SID of non-rac database and credential. User with sysdba role is required to perform conversion

SourceDBInfo SID=ORCL

Credentials

User sys User

Password password Password

RolesysdbaRole

Credentials

SourceDBInfo

ASMInfo element is required only if the current non-rac database uses ASM Storage

ASMInfo SID=+ASM1

Credentials

User sys User

Password passwordasm Password

RolesysdbaRole

Credentials

ASMInfo

Specify the list of nodes that should have rac instances running. LocalNode should be the first node in this nodelist.

NodeList

Node name=rac01

Node name=rac02

NodeList

Specify prefix for rac instances. It can be same as the instance name for non-rac database or different. The instance number will be attached to this prefix.

InstancePrefix ORCL InstancePrefix

!--Specify port for the listener to be configured for rac database.If port=, alistener existing on localhost will be used for rac

database.The listener will be extended to all nodes in the nodelist --

Listener port=""

Specify the type of storage to be used by rac database. Allowable values are CFS|ASM. The non-rac database should have same storage type.

SharedStorage type=ASM

Specify Database Area Location to be configured for rac database.If this field is left empty, current storage will be used for rac database. For CFS, this field will have directory path.

TargetDatabaseArea+DG_ORCL TargetDatabaseArea

Specify Flash Recovery Area to be configured for rac database. If this field is left empty, current recovery area of non-rac dat

abase will be configured for rac database. If current database is not using recovery Area, the resulting rac database will not have

a recovery area.

TargetFlashRecoveryArea +DG_ORCL Target FlashRecoveryArea

SharedStorage

Convert

ConvertToRAC

RConfig

===

Once you modify the convert.xml file according to your environment, use the following command to run the tool:

rconfig convertdb.xml

Oracle does the following during conversion:

oracle@usogp01: /u00/app/oracle/product/10.2.0/db_1/bin # ./rconfig convertdb.xml

Converting Database ORCL. to Cluster Database. Target Oracle Home : /u00/app/oracle/product/10.2.0/db_1.

Setting Data Files and Control Files

Adding Database Instances

Adding Redo Logs

Enabling threads for all Database Instances

Setting TEMP tablespace

Adding UNDO tablespaces

Adding Trace files

Setting Flash Recovery Area

Updating Oratab

Creating Password file(s)

Configuring Listeners

Configuring related CRS resources

Adding NetService entries

Starting Cluster Database

Starting Listeners

Operation Succeeded

/u00/app/oracle/product/10.2.0/db_1

ORCL1<\SID>

ORCL2<\SID>

<\SIDList>

oracle@usogp01: /u00/app/oracle/product/10.2.0/db_1/bin #

==

As you can see the exit code is 0, which indicates the success of the conversion process.

Uphills faced during the conversion process

The only error which I got during the conversion was missing listener.ora under ORACLE_HOME/network/admin directory as we had created listener under the ASM home (we have separate RDBMS and ASM homes). Problem has been resolved by creating the link under the RDBMS network admin directory of ASM listener.ora file.

We have contacted one of the developer of rconfig utiity to clarify the two changes (following) to make sure it will work:

non-ASM to ASM

single instance to RAC.

He said that the main purpose of this utility is to convert single instance database to RAC and optinally few non-ASM of target database to ASM while converting to RAC. But, he is not sure about moving the entire non-ASM to ASM while conversion. As they don't have any test case. However, asked us to go ahed and try. Well, we were amazed that it worked for us and we delightly informed the developer that two changes are worked for us and you can have our reference.

Our next migration/conversion is our EBusiness suit and of course, I am going to share my expereince here.

References

http://download-west.oracle.com/docs/cd/B19306_01/install.102/b14201/cvrt2rac.htm#sthref1273

Important Meta Link Notes:

ML Note:387046.1 RCONFIG : Frequently Asked Questions

ML Note:371519.1 RCONFIG Error When Converting From Single Instance To RAC

ML Note:375472.1 RCONFIG FAILS with an null pointer EXCEPTION

ML Note:391297.1 RCONFIG fails when using ASM as Shared Storage

ML Note:388577.1 Using Oracle 10g Release 2 Real Application Clusters and

Automatic Storage Management with Oracle E-Business Suite

Release 12

Happy Reading,

RMAN> convert database new database 'ORCL'
transport script '/tmp_backup/tempdb/transport.sql'
to platform 'HP-UX IA (64-bit)'
db_file_name_convert
'+DG_ORCL/ORCL/datafile','/tmp_backup/tempdb';

this command means convert an ASM database on AIX to filesystem on HPUX?

How we faster the process of converting a non-ASM single-instance database to RAC database with ASM using RCONFIG tool?

I have been given with a challenging task to convert one of our critical production databases, which is of 1 TB (Terabyte) in size, to Oracle 10g RAC with ASM storage option. Even though, there are many methods and tools available to perform this activity, I have preferred to use the RCONFIG tool.

We prepared an input XML file required for RCONFIG tool, and run the RCONFIG utility as follows:

$ cd /oracle/ora102/db_1/assistants/rconfig/sampleXMLs
$ rconfig ConverToRAC.xml

When we start the RCONFIG tool to convert the database to RAC, the RCONFIG tool initially moves all the non-ASM database files to ASM disk files, for this RCONFIG tool internally invokes RMAN utility to backup the target database to the ASM disk groups, eventually the database is converted to RAC using RCONFIG.

The conversion took almost 9 hours to complete the process, because during the conversion RMAN used only one channel per data file to backup to ASM disks. There was no chance of improving the RMAN copy process by allocating more channels in the input XML file, and also Oracle doesn’t recommend doing other changes in the input XML file.

One thing was observed during the RMAN copy that RMAN is using target database control file instead of recovery catalog, and also using the RMAN default preconfigured settings for that database.

To know the RMAN default preconfigured settings for the database:

$ export ORACLE_SID=MYPROD
$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 5 10:21:05 2009

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

connected to target database: MYPROD (DBID=1131234567)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/ora102/db_1/dbs/snapcf_T24MIG1.f'; # default

Here we see that the PARALLELISM is 1 (default), that’s why the RMAN using only one channel during backing up the non-ASM datafiles to ASM Disk Groups, and were taking 9 hours to complete the backup.

We have changed the PRALLELISM count to 6 (it depends upon number of CPUs you have in the server).

Solution:

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 6;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/ora102/db_1/dbs/snapcf_T24MIG1.f'; # default

After changing the PARALLELISM count to 6, the RMAN has allocated 6 channels and the conversion process has improved greatly and reduced the downtime drastically to 4 Hours 30 minutes.

Following is the extract of rconfig.log file, this file is located under:

$ORACLE_HOME/db_1/cfgtoolslogs/rconfig
............................................................................
............................................................................
............................................................................
[17:17:16:43] Log RMAN Output=RMAN> backup as copy database to destination '+DATA_DG';
[17:17:16:53] Log RMAN Output=Starting backup at 04-AUG-09
[17:17:16:258] Log RMAN Output=using target database control file instead of recovery catalog
[17:17:16:694] Log RMAN Output=allocated channel: ORA_DISK_1
[17:17:16:698] Log RMAN Output=channel ORA_DISK_1: sid=866 devtype=DISK
[17:17:17:9] Log RMAN Output=allocated channel: ORA_DISK_2
[17:17:17:13] Log RMAN Output=channel ORA_DISK_2: sid=865 devtype=DISK
[17:17:17:324] Log RMAN Output=allocated channel: ORA_DISK_3
[17:17:17:327] Log RMAN Output=channel ORA_DISK_3: sid=864 devtype=DISK
[17:17:17:637] Log RMAN Output=allocated channel: ORA_DISK_4
[17:17:17:641] Log RMAN Output=channel ORA_DISK_4: sid=863 devtype=DISK
[17:17:17:967] Log RMAN Output=allocated channel: ORA_DISK_5
[17:17:17:971] Log RMAN Output=channel ORA_DISK_5: sid=862 devtype=DISK
[17:17:18:288] Log RMAN Output=allocated channel: ORA_DISK_6
[17:17:18:293] Log RMAN Output=channel ORA_DISK_6: sid=861 devtype=DISK
[17:17:20:416] Log RMAN Output=channel ORA_DISK_1: starting datafile copy
[17:17:20:427] Log RMAN Output=input datafile fno=00053 name=/oradata/MYPROD/users_01.dbf
[17:17:20:532] Log RMAN Output=channel ORA_DISK_2: starting datafile copy
[17:17:20:544] Log RMAN Output=input datafile fno=00021 name=/oradata/MYPROD/ users_02.dbf
[17:17:20:680] Log RMAN Output=channel ORA_DISK_3: starting datafile copy
[17:17:20:694] Log RMAN Output=input datafile fno=00022 name=/oradata/MYPROD/ users_03.dbf
[17:17:20:786] Log RMAN Output=channel ORA_DISK_4: starting datafile copy
[17:17:20:800] Log RMAN Output=input datafile fno=00023 name=/oradata/MYPROD/ users_04.dbf
[17:17:20:855] Log RMAN Output=channel ORA_DISK_5: starting datafile copy
[17:17:20:868] Log RMAN Output=input datafile fno=00024 name=/oradata/MYPROD/ users_05.dbf
[17:17:20:920] Log RMAN Output=channel ORA_DISK_6: starting datafile copy
[17:17:20:930] Log RMAN Output=input datafile fno=00011 name=/oradata/MYPROD/ users_06.dbf
............................................................................
............................................................................
............................................................................
[21:29:5:518] Log RMAN Output=Finished backup at 04-AUG-09
............................................................................
............................................................................
............................................................................

[21:39:10:723] [NetConfig.startListenerResources:5] started Listeners associated with database MYPROD
[21:39:10:723] [Step.execute:255] STEP Result=Operation Succeeded
[21:39:10:724] [Step.execute:284] Returning result:Operation Succeeded
[21:39:10:724] [RConfigEngine.execute:68] bAsyncJob=false
[21:39:10:725] [RConfigEngine.execute:77] Result= < version="1.1">

&ltConvertToRAC>
   &ltConvert>
     &ltResponse>
       &ltResult code="0" >
         Operation Succeeded
       
     
     &ltReturnValue type="object">
&ltOracle_Home>
        /oracle/ora102/db_1
      
       &ltSIDList>
         &ltSID&gtMYPROD1<\SID>
         &ltSID&gtMYPROD2<\SID>
       <\SIDList>     
   
 

Note: For the sake of look and feel format, the above output has been trimmed neatly. You can also observer that 6 channels were being allocated, timings of backup start and end, and the success code end of the rconfig.log file.

References:

To know more about RCONFIG tool and other Metalink references on it, please take a look at the below blog post written by Mr. Syed Jaffar Hussain.

http://jaffardba.blogspot.com/2008/09/my-experience-of-converting-cross.html

Oracle 10g R2 Documentation information on RCONFIG:

http://download.oracle.com/docs/cd/B19306_01/install.102/b14205/cvrt2rac.htm#BABBAAEH

Block Corruption and Recovery

Step 1: Identify the corrupt blocks
-----------------------------------
1. Run below command to populate v$database_block_corruption view with information of all the corrupted blocks.

RMAN> backup validate check logical database;

“CHECK LOGICAL" option is used to identify both Physical and Logical Block Corruptions.

Select the view to identify the corrupted blocks detected by RMAN.
SQL> select * from v$database_block_corruption;

Please note, After a corrupt block is repaired, the row identifying the block is deleted from the view.

2. Check alert. log file for corrupted blocks, data file list.
For ex:- ORA-01578: ORACLE data block corrupted (file # 5, block # 15)
ORA-01110: data file 5: '/oracle/oradata/trgt/users01.dbf'

3. You can also use dbverify utility to identify Physical and Logical Intra Block Corruptions.
dbv file=datafile_name blocksize=datafile_block_size

Step 2: Recovering Data blocks
-------------------------------
1. Recovering Data blocks By Using All Available Backups

Run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks

RMAN>BLOCKRECOVER DATAFILE 5 BLOCK 15;

Recover multiple blocks in single command
RMAN>BLOCKRECOVER DATAFILE 5 BLOCK 15 DATAFILE 2 BLOCK 10;

2. Recovering Data blocks Using Selected Backups

Run the BLOCKRECOVER command at the RMAN prompt, Specifying the data file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, specify what type of backup should be used to restore the blocks.

# restore from backupset
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM BACKUPSET;

# restore from datafile image copy
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM DATAFILECOPY;

# restore from backup set with tag "Sunday"
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM TAG = Sunday;

# restore using backups created before log sequence 100
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL SEQUENCE 100;

# restore using one week ago backups
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL 'SYSDATE-7';

# restore using backups until SCN 100
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL SCN 100;

3 . Recovering blocks listed in V$DATABASE_BLOCK_CORRUPTION view

Run the below command to recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION
RMAN> BLOCKRECOVER CORRUPTION LIST;

Restores blocks from backup sets created more than 7 days ago
RMAN> BLOCKRECOVER CORRUPTION LIST FROM BACKUPSET RESTORE UNTIL TIME 'SYSDATE-7';

Note:-Block corruptions in RMAN backups and copies is kept in V$BACKUP_CORRUPTION and V$COPY_CORRUPTION

Step 3: Allow Recovery to corrupt blocks
-----------------------------------------
During recovery database finds corrupt blocks then recovery stops. Run the below command in order to skip the corrupt blocks and proceed with recovery.

SQL>RECOVER DATABASE ALLOW n CORRUPTION;

Where n is the number of allowable corrupt blocks

Oracle EBS-Demantra Integration Patch

Install Oracle EBS-Demantra Integration Patch

Download Oracle EBS-Demantra Integration Patch(6606568)
1. Apply this patch in Your Oracle R12 EBS

2. If Demantra is installed after this patch has been applied, please run msddemcrsyn.sql from $MSD_TOP/patch/115/sql.

After Apply Patch
1. Login System Administrator Responsibility
2. Nav: Profile -> System
3. Query profile MSD_DEM: Host URL.
4. Change value to http://hostname:port/demantra e.g (http://sys50.doyen.in:8080/demantra)
5. Save.
6. Run the following script on the ASCP/MSC instance which also contains the Demantra Schema to determine the actual schema name for Demantra:

select owner
from all_tables
where table_name = 'SALES_DATA';

7. Set profile option MSD_DEM: Schema = to the value returned from the script in step 1 at the site level and save. System Administrator/Profiles/System - query for MSD_DEM:% - select the Find button - Find/scroll to the profile option MSD_DEM: Schema - set the correct value at the site level - save

8.SQL>select fnd_profile.value('MSD_DEM_SCHEMA') from dual;

9.SQL>select fnd_profile.value('MSD_DEM_HOST_URL') from dual;

10.Login Demand Management System Administrator

Collaborator Workbench
http://sys50.doyen.in:8080/demantra/portal/loginpage.jsp

Web client
http://sys50.doyen.in:8080/demantra/portal/partnerLogin.jsp

Oracle Demantra Anywhere version of Collaborator Workbench
http://sys50.doyen.in:8080/demantra/portal/remoteloginpage.jsp

Oracle Demantra Anywhere version of Web client alone
http://sys50.doyen.in:8080/demantra/portal/anywhereLogin.jsp

Offline access to Oracle Demantra worksheets
http://sys50.doyen.in:8080/demantra/portal/launchDPWeb.jnlp

Dynamic Open Link (DOL) access for third-party reporting tools
http://sys50.doyen.in:8080/demantra/portal/DOL_HTML.htm

User Management
http://sys50.doyen.in:8080/demantra/portal/userManagement.jsp

Collaborator Workbench Administration
http://sys50.doyen.in:8080/demantra/portal/adminLogin.jsp

Workflow Manager
http://sys50.doyen.in:8080/demantra/workflow/login.jsp

Technical Administration
http://sys50.doyen.in:8080/demantra/admin

Metalink ID: 434991.1 - EBS-Demantra Integration Installation Overview and Diagram

How this Rapid Wizard installation works are

The basics of how this Rapid Wizard installation works are:-

1.RapidWizard collects user information required for configuration of system, and creates a configuration file
2.RapidWizard checks Port availability and file space required
3.Zip files from the "oraDB and oraAppDB" DVD stage area are then uncompressed onto the file system
4.Each Disk directory contains a driver which is read to control the unzip phase
5.The DB Techstack is relinked and reconfigured using an install driver, templates and the configuration file
6.Control files are created
7.The RDBMS ORACLE_HOME is registered into the Global Inventory
8.The DB is started, and further configuration is performed
9.Zip files from the "oraAS" (Developer Tools and As10g" DVD stage area are then uncompressed onto the file system
10.The Apps Techstack is relinked and reconfigured using the install driver, templates and the configuration file
11.The Developer Tools and As10g ORACLE_HOMEs are registered into the Global Inventory
12.Zip files from the "oraApps" (APPL_TOP, COMMON_TOP etc) stage are uncompressed onto the file system
13.APPL_TOP is reconfigured with system using values in the configuration file and services are started
14.Post install checks are performed to check success of the installation

Oracle Apps EBS R12 Installing Log Files

Where are the log files?

The following are referenced in this document

{MMDDHHMM} : MM (Month) DD (Day) HH (Hour) MM (Minute)
{Timestamp} : {This may include Year, Month, Day, and Time}
{Base Install} : Location chosen during the RapidWiz configuration phase
$CONTEXT_NAME : {SID}_{hostname}
RDBMS $ORACLE_HOME} : {Base Install}/db/tech_st/10.2.0
$IAS_ORACLE_HOME : {Base Install}/apps/tech_st/10.1.3
Tools $ORACLE_HOME : {Base Install}/apps/tech_st/10.1.2
$INST_TOP : {Base Install}/inst/apps/$CONTEXT_NAME
$APPL_TOP : {Base Install}/apps/apps_st/appl

Before the installation starts:

$TMP/{MMDDHHMM}/{MMDDHHMM}.log$TMP/{MMDDHHMM}/{hostname}_{SID}.xml
$TMP/{MMDDHHMM}/{hostname}_{SID}_apps.xml

The Rapidwiz Configuration File is saved in 3 locations:-

$TMP/{MMDDHHMM}/conf_{SID}.txt$INST_TOP/conf_{SID}.txt{RDBMS ORACLE_HOME}/appsutil/conf_{SID}.txt

Database Tier:

RDBMS $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/{MMDDHHMM}.log
RDBMS $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTechStack_{MMDDHHMM}.log
RDBMS $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ohclone.log
RDBMS $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/make_{MMDDHHMM}.log
RDBMS $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/installdbf.log
RDBMS $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/adcrdb_{SID}.log
RDBMS $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDatabase_{MMDDHHMM}.log
RDBMS $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/{MMDDHHMM}/adconfig.log
RDBMS $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/{MMDDHHMM}/NetServiceHandler.log

Applications Tier(s):

$INST_TOP/logs/{MMDDHHMM}.log
$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppsTechStack.log
$INST_TOP/logs/ora/10.1.2/install/make_{MMDDHHMM}.log
$INST_TOP/logs/ora/10.1.3/install/make_{MMDDHHMM}.log
$INST_TOP/admin/log/ApplyAppsTechStack.log
$INST_TOP/admin/log/ohclone.log
$APPL_TOP/admin/$CONTEXT_NAME/log/installAppl.log
$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppltop_{MMDDHHMM}.log
$APPL_TOP/admin/$CONTEXT_NAME/log/{MMDDHHMM}/adconfig.log
$APPL_TOP/admin/$CONTEXT_NAME/log/{MMDDHHMM}/NetServiceHandler.log

Inventory Registration:

{GlobalInventory}/logs/cloneActions{timestamp}.log{GlobalInventory}/logs/oraInstall{timestamp}.log{Global Inventory} /logs/silentInstall{timestamp}.log

Killiing the Standard Manger when one or instances running on the same server

Standard Manger Killing:-

set linesize 120
set head off
set pages 0
select 'kill -9 '||' '|| substr(b.os_process_id,0,10) "OS Proc"
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='STANDARD'
and b.process_status_code='A'
order by b.process_status_code;

Debug or Trace Option Enabled in Profiles

SELECT A.CONCURRENT_PROGRAM_NAME "Program Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User Program Name",
SUBSTR(B.USER_NAME,1,15) "Last Updated By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;

Run this script to see if any Debug or Trace profile options have been set to Y, meaning that they are enabled. Some profile options may be required to be set to Y, but others should be N.

select distinct
a.application_short_name app_short,
user_profile_option_name optname,
decode(level_id,
10001,'SITE',
10002,'APP : '||a2.application_short_name,
10003,'RESP: '||r.responsibility_key,
10004,'USER: '||u.user_name,
'Unknown') d_level,
profile_option_value optval,
v.last_update_date updated
from fnd_profile_options_vl o,
fnd_profile_option_values v,
fnd_application a,
fnd_application a2,
fnd_responsibility r,
fnd_user u
where (
o.user_profile_option_name like '%Debug%' or
o.user_profile_option_name like '%DEBUG%' or
o.user_profile_option_name like '%Trace%' or
o.user_profile_option_name like '%TRACE%'
)
and a.application_id = v.application_id
and o.application_id = v.application_id
and o.profile_option_id = v.profile_option_id
-- Find the associate level for profile
and r.application_id (+) = v.level_value_application_id
and r.responsibility_id (+) = v.level_value
and a2.application_id (+) = v.level_value
and u.user_id (+) = v.level_value
and profile_option_value = 'Y'
order by 2,1,3,4;

http://oracle-apps-dba.blogspot.com/2007/07/apps-user-connection-details.html

And here is Modified version that shows and requests as well others session details.
Remark:
it use input parameter to reduce result on only named apps user
2) It should be run as apps user or use "set current_schema" part in front.
set show off
SET VER OFF
set head on;
set timing on;


undefine apps_user;
accept apps_user char prompt 'Input starting letters of APPS username (Enter for all...): ';

set linesize 300;
set pagesize 200;

col sid_serial for a13;
col user_name for A10;
col module for a22;
col Responsibility for a29;
col function for a30;
col F_Type for a10;
col ap_pid for a6;
col db_pid for a6;

break on USER_NAME on db_pid on ap_pid on sid_serial

select * from (
select
usr.user_name user_name
,v.spid db_pid
,ses.process ap_pid
,ses.sid||','||ses.serial# sid_serial
,ses.module
,rsp.responsibility_name Responsibility
,fuc.function_name Function
,i.function_type F_Type
,to_char(i.last_connect,'dd.mm hh24:mi') F_Start
from
apps.icx_sessions i
,apps.fnd_logins l
,apps.fnd_appl_sessions a
,apps.fnd_user usr
,apps.fnd_responsibility_tl rsp
,apps.fnd_form_functions fuc
,gv$process v
,gv$session ses
where i.disabled_flag = 'N'
and i.login_id = l.login_id
and l.end_time is null
and i.user_id = usr.user_id
and l.login_id = a.login_id
and a.audsid = ses.audsid
and l.pid = v.pid
and l.serial# = v.serial#
and i.responsibility_application_id = rsp.application_id(+)
and i.responsibility_id = rsp.responsibility_id(+)
and i.function_id = fuc.function_id(+)
and i.responsibility_id not in (select t1.responsibility_id
from apps.fnd_login_responsibilities t1
where t1.login_id = l.login_id
)
and usr.user_name like '&apps_user%'
union
select
usr.user_name
,v.spid
,ses.process
,ses.sid||','||ses.serial# sid_serial
,ses.module
,rsp.responsibility_name
,null
,null
,null form_start_time
from
apps.fnd_logins l
,apps.fnd_login_responsibilities r
,apps.fnd_user usr
,apps.fnd_responsibility_tl rsp
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = r.login_id(+)
and r.end_time is null
and r.responsibility_id = rsp.responsibility_id(+)
and r.resp_appl_id = rsp.application_id(+)
and r.audsid = ses.audsid
and usr.user_name like '&apps_user%'
union
select
usr.user_name
,v.spid
,ses.process
,ses.sid||','||ses.serial# sid_serial
,ses.module
,null
,frm.user_form_name
,ff.type
,to_char(f.start_time,'dd.mm hh24:mi')
from
apps.fnd_logins l
,apps.fnd_login_resp_forms f
,apps.fnd_user usr
,apps.fnd_form_tl frm
,apps.fnd_form_functions ff
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = f.login_id(+)
and f.end_time is null
and f.form_id = frm.form_id(+)
and f.form_appl_id = frm.application_id(+)
and f.audsid = ses.audsid
and ff.form_id = frm.form_id
and usr.user_name like '&apps_user%'
union
select
fu.user_name
,cr.oracle_process_id
,cr.OS_PROCESS_ID
,vs.SID || ',' || vs.serial# sid_serial
,(SELECT cp.concurrent_program_name
FROM APPS.fnd_concurrent_programs cp
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
)
,(select responsibility_name
from apps.fnd_responsibility_vl
where responsibility_id = cr.responsibility_id
and application_id = cr.responsibility_application_id
)
,(select execution_file_name
from apps.fnd_executables fe, apps.fnd_concurrent_programs fcp
where fcp.concurrent_program_id = cr.concurrent_program_id
and fcp.application_id = cr.program_application_id
and fe.executable_id = fcp.executable_id
and fe.application_id = fcp.executable_application_id
)
,'REQUEST'
,TO_CHAR(NVL(cr.actual_start_date, cr.requested_start_date), 'DD.MM HH24:MI') start_time
FROM
APPS.fnd_concurrent_requests cr,
v$process vp,
v$session vs,
apps.fnd_user fu
WHERE
cr.phase_code <> 'I'
AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
/*
(cr.phase_code < 'I' OR cr.phase_code > 'I')
AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
*/
AND cr.status_code NOT IN ('U', 'X', 'D', 'E', 'I', 'C')
AND cr.oracle_process_id = vp.spid (+)
AND cr.oracle_session_id = vs.audsid (+)
AND fu.user_id = cr.requested_by
AND vs.sid is not null
and fu.user_name like '&apps_user%'
)
order by user_name, db_pid, ap_pid, sid_serial
;