Control Files Migration.

Control Files for Migration this article discuss the procedure to generate the auto control files for given

schema and store the control files in text format.This process saves lot of time and effort. I assume the oracle

directory is created before running this process.This procedure reads the schema tables and generate generic

control file based on data types. For each table process generates one control file and stores in default oracle

directory path.

Create Or Replace Procedure CreateControlFiles(PDateFormat VarChar2)Is

Cursor C1 Is Select

'LOAD DATA' chr (10) 'INFILE ''' lower (table_name) '.dat'''

chr (10) 'INTO TABLE ' table_name chr (10)

'FIELDS TERMINATED BY '''''chr (10)'TRAILING NULLCOLS'

chr (10) '(' CtrText,Table_NameFrom User_Tables;

Cursor C2(CvTableName VarChar2) Is

Select Decode(Column_Id, 1, '', ' , ')Rpad(Column_Name, 33, ' ')

Decode (Data_Type,'VARCHAR2',

'CHAR NULLIF ('Column_Name'=BLANKS)','FLOAT',

'DECIMAL EXTERNAL NULLIF('Column_Name'=BLANKS)','NUMBER',

Decode (Data_Precision, 0,'INTEGER EXTERNAL NULLIF ('Column_Name'=BLANKS)',

Decode (Data_Scale, 0,'INTEGER EXTERNAL NULLIF

('Column_Name'=BLANKS)','DECIMAL EXTERNAL NULLIF

('Column_Name'=BLANKS)')),'DATE',

'DATE "'PDateFormat'" NULLIF ('Column_Name'=BLANKS)', Null)ColTextFrom User_Tab_ColumnsWhere Table_Name = CvTableNameOrder By Column_Id;

Backup & Recovery

In this document, I discussed how to take backup (source) and recover (target/other m/c) the database

in different machine in NOARCHIVE MODE.

Following are two important parameters which need to set accordingly.

Alter System Set
db_recovery_file_dest_size=20G scope=both
/
Alter System Set
db_recovery_file_dest= ''F:\oracle\product\10.2.0\flash_recovery_area'' scope=both
/
Taking Backup (RMAN)

1. Shut down Database

Sql> Shutdown immediate;

2. Startup database in MOUNT mode.

Sql> Startup Mount;

3. Now start RMAN (by typing RMAN at command prompt)

C:> RMAN TARGET /

4. Check the configuration

RMAN> SHOW ALL;

5. Check for default device and CONTROLFILE AUTOBACKUP parameters.

Default Device should be DISK (if backup is on harddisk)

CONTROLFILE AUTOBACKUP should be ON; (This command will take the control files and spfile auto backup).

Execute below statements

RMAN>CONFIGURE DEFAULT DEVICE TYPE TO DISK;

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP On;

6. Take the backup

RMAN> BACKUP DATABASE;

General Ledger Setup Steps In Oracle Applications

Step 1 : Define GL Responsibility

System Administrator =>security=>responsibility.

Step2 : Define GL User

System Administrator => security=>user

Step3 :-Assign GL and Sysadmin Responsibility to User.

Step4 :-select profile option (HR:User Type) to HR With Payroll User.

Step5 : Enable Cyrrency

Define Currency and Enable Currency :-

GL Super User Responsibility =>setup=>currencies =>define.




Step 6 :- Define Organization –Business Group & add Business Group Information Also.

Make your Organization HR Organization also.

Responsibility : India HRMS Manager.

Work structure=>organization=>Decsription

Create Location If Required.

Responsibility : India HRMS Manager.

Work structure=>Location.

Add Business Group Information.

Create Competence flexfield structure required to enter in Business group information.

Step 7:- Set profile option HR Security Profile.

Step 8:- Define Key Flexfield (Chart Of Accounts In GL).

Define value sets for segments.

Define segments and assign value sets.

Assign qualifires to value sets.

Freeze and compile.

Responsibility = > system administration => application => segments

Flexfield Quilifires :-

Creating VALUE SETS for segments and Enter The Segment values :-

Step 9:- Calendars.

Define calendars Type.


GL Super User Responsibility =>setup=>financials=>calendars=>types


Define calendar Accounting.


GL Super User Responsibility =>setup=>financials=>calendars=>accounting.


Step 10 :- Define Set of Books.


GL Super User Responsibility => setup=> financials =>books=>define.


Link Chart Of Account, calendar & currency.

Here we enter the Retained Earnings Account which
means the profit and loss account.
No user can pass the direct entry in this account.


Step 11:- Define Organization ( India HRMS=> organization => Decsription.)

Legal Entity (also legal entity additional information=>legal entity accounting
set to newly created set of books name), Operating Unit.

Step 12 :- Set profile options.

System administrator => profile => system.

MO: Operating Unit

GL Set of Books Name

Sequential Numbering.

Step 13:- Open accounting period.

Once these setup steps are performed, we can
Enter Journals
Post Journals
Reverse Journals
Enter Mass allocation.
Create Budgets
Use FSG ( financial statement Generator.)