Cloning from Oracle EBS r12 RAC to non-RAC

This document explains the full steps for how to clone an RAC instance to non-RAC using oracle 10g RMAN duplicate database command, and backup is stored on the file system.

The below are the details steps which can be used to implement this solution:

Step 1) Run Preclone script on both Dbtier and AppsTier on the SOURCE system.

Step 2) Create backup on SOURCE Databases to FILESYSTEM (è /backup)

run {
allocate channel d1 type disk;
backup format '/backup/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backup/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}

Step 3) compress database ORACLE HOME on the source node, then move it to the

source node using GZIP utility as the following:

tar cvf – DB_Home | gzip > DB_Home.tar.gz

Step 4) Copy the tar file of the Oracle HOME which has been taken in the previous step

to the target node and extract this file.

Step 5) Copy the backup pieces which has been taken in step2 to the TARGET node, and in the

same location where the backup pieces has been taken in step 2 (è /backup)

Step 6) Run adcfgclone.pl script on the TARGET db node using the following command:

perl adcfgclone.pl dbTechStack
This will create the init parameter file, context file and environment files on the target

node.

Step 7) now on the TARGET node and as oracle user, create the directories where the

datafiles,onlinelogs and tempfiles are stored on your ASM storage:

SOURCE NODE TARGET NODE

+DATA1/prod/datafile è $ mkdir $DATA_TOP/prod/datafile

+DATA1/prod/onlinelog è $ mkdir $DATA_TOP/prod/onlinelog

+DATA1/prod/tempfile è $ mkdir $DATA_TOP/prod/tempfile

Suppose that the DATA_TOP on the target node is /u01/app/CLONE/oradata

Step 8) Change initialization parameters on the init file on the target instance:
db_file_name_convert =('+DATA1, '/u01/app/CLONE/oradata')
log_file_name_convert =('+DATA1', '/u01/app/CLONE/oradata')

Step 9) now on the target node, startup the database in nomount mode

Step 10) check that you can access the source database, by copying tnsEntries

from the SOURCE Node to the Target node tnsnames.ora file.

Step 11) on the target node, connect to the RMAN using the following command, where

PROD is your source database, and CLONE is the target database:

$ rman target sys/sys@PROD auxiliary /

RMAN>

run
{

allocate auxiliary channel ch1 type disk;
duplicate target database to CLONE;
release channel ch1;
}

Step 12) After the RMAN command completed successfully, shutdown the database,

then change the following database initialization parameters, in init parameter file:

_no_recovery_through_resetlogs=TRUE
undo_management to =MANUAL

undo_tablespace=UNDOTBS1

Now open the database with RESETLOGS option;

Step 13) Create APPS_UNDOTS1 tablespace using the following statements:

CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE

'/u01/app/CLONE/oradata/prod/datafile/undo01.dbf' SIZE 4000M AUTOEXTEND

ON NEXT 100M MAXSIZE 7000M

ONLINE

RETENTION NOGUARANTEE

BLOCKSIZE 8K

FLASHBACK ON;

Step 14) now edit the init parameter file, and change the following initialization

parameters

undo_management to =AUTO

undo_tablespace= APPS_UNDOTS1

and remove the added initialization arameter_no_recovery_through_resetlogs=TRUE

Drop all unused undo tablespaces

Step 15) Disable database archive on the TARGET database, and start the database in

open mode

Step 16) On the target database, run the library update script against the database

$ cd [RDBMS ORACLE_HOME]/appsutil/install/[CONTEXT NAME]
$ sqlplus "/ as sysdba" @adupdlib.sql [libext]

Where [libext] should be set to 'sl' for HP-UX, 'so' for any other UNIX platform, or 'dll' for Windows.

Step 17) Configure the target database, the database must be running and open before performing this step.

$ cd [RDBMS ORACLE_HOME]/appsutil/clone/bin
$ perl adcfgclone.pl dbconfig [Database target context file]

Where Database target context file is: [RDBMS ORACLE_HOME]/appsutil/[Target CONTEXT_NAME].xml.

Step 18) Check the database links

References:

- Cloning Oracle Application 11i /R12 with Rapid Clone - Database (9i/10g/11g) Using Hot Backup on Open Database [ID 760772.1]

Get the Oracle cluster Name and Version

To get the Cluster name and Version:
Under $ORACLE_CRS_HOME/bin:

$ cemutlo -n -w

-n: Get the cluster name
-w: Get the cluster version

To just get the cluster name only you can use:

ls $ORA_CRS_HOME/cdata
You will file a directory named as cluster name include the automatic OCR backups

How To Enable/Disable Submit Another Request in Apps R12

Profile Option "Concurrent: Show Requests Summary After Each Request" controls the behaviour of the application after request submission:

If set to Yes, the View Requests form displayed.

If set to No, the Decision Window displayed.

Oracle RAC TAF Policy

TAF allows the DBA to configure the type and method of failover for each Oracle Net client.

TAF Policy: failover methods
1-None : Dont use TAF
2-Basic : Establish connection at failover time only
3-Pre-connect : Establish connection to both prefered instance and backup instance.

TAF Types:
1- Select : Oracle Net keeps track of all SELECT statements. tracking how many rows have
been fetched back to the client for each cursor associated with a SELECT statement.
If the connection to the instance is lost, Oracle Net establishes a connection to
another Oracle RAC node and re-executes the SELECT statements, repositioning the
cursors so the client can continue fetching rows as if nothing has happened.
The SELECT failover approach is best for data warehouse systems that perform complex
and time-consuming transactions.
2- Session: When the connection to an instance is lost, SESSION failover results only in the
establishment of a new connection to another Oracle RAC node; any work in progress is
lost. SESSION failover is ideal for online transaction processing (OLTP) systems,
where transactions are small.

Start and Stop Oracle Cluster

To stop the cluster and all services use those commands on sequance:
srvctl stop instance -d racdb -i racdb1
srvctl stop asm -n rac1
srvctl stop nodeapps -n rac1

To start the cluster and all services use those commands on sequance:
srvctl start nodeapps -n rac1
srvctl start asm -n rac1
srvctl start instance -d racdb -i racdb1

To do those in one shot use:
srvctl start database -d racdb
srvctl stop database -d racdb

Extract number from string in oracle

Use this to extract number from string in oracle pl/sql:

SELECT REPLACE(REGEXP_REPLACE('123 abc' , '[a-z]', ''),' ')
FROM dual;