Syteline Audit Log How to

Enabling Audit Logging Feature

1. Process Default Form

First need to enable audit logging by entering 1 in the form Process Defaults - Enable audit logging

2. Audit Log Types

Need to define database table and field to capture log in the Audit Log Types form.

Message Type numbers 1 - 9999 are reserved for Syteline to use so use the number above 10000.

3. Audit Log

To view open up Audit Log form and then enter Message Type number and then query.

RMAN - Restoring SPFILE and Control File

Recovering a Lost Control File

If you are running NOARCHIVELOG mode

rman target /

startup nomount;

restore controlfile from autobackup;

alter database mount;

recover database noredo;

alter database open resetlogs;

If you are running ARCHIVELOG mode, recovery is only slightly different:

rman target /

startup nomount;

restore controlfile from autobackup;

alter database mount;

recover database;

alter database open resetlogs;

Restoring the server parameter file (SPFILE)

If you lose your server parameter file (SPFILE), RMAN can restore it to its default location or to a location of your choice.  Unlike the loss of the control file, the loss of your SPFILE does not cause your instance to immediately stop.  Your instance may continue operating, although you will have to shut it down and restart it after restoring the SPFILE.

1. If the database is up at the time of the loss of the SPFILE, connect to the target database.

rman target /

If the database it not up when the SPFILE is lost, and you are not using a recovery catalog, then you must set the DBID of the target database.

2. Shut down the instance and restart it without mounting. When the SPFILE is not available, RMAN starts the instance with a dummy parameter file.

startup force nomount;

3. Restore the server parameter file. If restoring to the default location, then run:

restore spfile from autobackup;

If restoring to a nondefault location, then you could run commands as in the following example;

restore spfile to '/home/oracle/spfileTEMP.ora' from autobackup;

4. Restart the instance with the restored file. If restarting with a server parameter file in a nondefault location, then create a new client-side initialization parameter file with the single line SPFILE=new_location, where new_location is the path name of the restored server parameter file. Then, restart the instance with the client-side initialization parameter file.

For example, create a file /tmp/init.ora which contains the single line:

SPFILE=/home/oracle/spfileTEMP.ora

Then use this RMAN command, to restart the instance based on the restored SPFILE:

startup force pfile=/tmp/init.ora;

OEM Deleting Alert Manually

Sometimes Oracle Enterprise Manager alert was triggered and will not go away.  You cannot even delete those alert from the OEM User Interface and wait for the alerts to go away automatically but it never goes away.




When this happen here is some option you can try to remove those alerts.

Loggon to OEM server and login as sysman.

select target_guid, metric_guid, key_value, message from mgmt_current_severity 

where message like 'User SYS logged on %';

TARGET_GUID                      METRIC_GUID
-------------------------------- --------------------------------
KEY_VALUE
-------------------------
MESSAGE
--------------------------------------------------------------------------------
BF0B06F8FE2A18DBD8CB3F89C6503995 D6438569B496BC9205481E8A70F92F1E
SYS_grape
User SYS logged on from grape.

exec EM_SEVERITY.delete_current_severity(TARGET_GUID,METRIC_GUID,KEY_VALUE);

 SQL> exec EM_SEVERITY.delete_current_severity('BF0B06F8FE2A18DBD8CB3F89C6503995'
,'D6438569B496BC9205481E8A70F92F1E','SYS_grape');
PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Inventory Period - Resubmitting Unprocessed Materials

I have a few records showing it is currently background processing and records are locked but actually they are not being processed.

From the Oracle Application Form you cannot re-submitting those records because the records are locked. Due to this error you cannot close your month-end inventory period.

Here is how you can re-submit those locked transaction via SQL-Plus

SQL> select process_flag, lock_flag, transaction_mode, error_code
from mtl_transactions_interface;


PROCESS_FLAG     LOCK_FLAG      TRANSACTION_MODE   ERROR_CODE
--------------------------------------------------------------------------------
           1                                1                                       3
           1                                1                                       3
           1                                1                                       3

SQL> update mtl_transactions_interface set process_flag = 1, lock_flag = 2, transaction_mode = 3, error_code = null where transaction_id = '&transaction_id';

3 rows updated.

SQL> commit;

Commit complete.

Records should be picked up by background process and it should process the records again.

Update Option Class BOM WIP_SUPPLY_TYPE

Using BOM_INVENTORY_COMPS_INTERFACE

SEGMENT1	OPTIONAL	WIP_SUPPLY_TYPE	MUTUALLY_EXCLUSIVE_OPTIONS
420K60D8	1	2	2
917Y66D8	1	2	2
156A66S4	1	2	2
156A66S8	1	2	2
157A66D4	1	2	2
157A66D8	1	2	2
157A66S4	1	2	2
157A66S8	1	2	2
156A66D4	1	2	2
156A66D8	1	2	2
437A60D4	1	2	2
437A60D8	1	2	2
437A60S4	1	2	2

 I have some data needs to change from WIP_SUPPLY_TYPE = 2 to WIP_SUPPLY_TYPE = 6.

WIP_SUPPLY_TYPE = 2 means Assembly Pull
WIP_SUPPLY_TYPE = 6 means Phantom.

Insert into apps.bom_inventory_comps_interface
(component_sequence_id, wip_supply_type, process_flag, transaction_type)
SELECT component_sequence_id, 6 wip_supply_type
, 1 process_flag, 'UPDATE' transaction_type
from (SELECT B.SEGMENT1
, D.SEGMENT1
, BIC.OPTIONAL
, BIC.COMPONENT_SEQUENCE_ID
, BIC.WIP_SUPPLY_TYPE
, BIC.MUTUALLY_EXCLUSIVE_OPTIONS
FROM apps.BOM_BILL_OF_MATERIALS A, apps.MTL_SYSTEM_ITEMS_B B, apps.BOM_INVENTORY_COMPONENTS BIC, apps.MTL_SYSTEM_ITEMS_B D
, apps.HR_ORGANIZATION_UNITS HR
WHERE A.ASSEMBLY_ITEM_ID = B.INVENTORY_ITEM_ID
AND BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.ORGANIZATION_ID = HR.ORGANIZATION_ID
AND HR.NAME LIKE '%Master%'
AND B.SEGMENT1 = 'SLAB'
AND BIC.DISABLE_DATE IS NULL
AND BIC.COMPONENT_ITEM_ID = D.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = B.ORGANIZATION_ID
AND BIC.WIP_SUPPLY_TYPE = 2
AND (D.SEGMENT1 LIKE '%8' OR D.SEGMENT1 LIKE '%4'))

HR.NAME is your Master Inventory Organization name
B.SEGMENT1 is one of your option classes name
D.SEGMENT1 is the element that you want to make changes to.

Basically above query will insert records into BOM_INVENTORY_COMPS_INTERFACE table with ITEMs ends with 8 or 4 and WIP_SUPPLY_TYPE as Assembly Pull under SLAB OPTION CLASS.

I want to update WIP_SUPPLY_TYPE CODE for above option class components to be PHANTOM which is WIP_SUPPLY_TYPE value as 6.

SQL> insert into apps.bom_inventory_comps_interface
(component_sequence_id, wip_supply_type, process_flag, transaction_type)
SELECT component_sequence_id, 6 wip_supply_type, 1 process_flag, 'UPDATE' transaction_type
from (SELECT B.SEGMENT1
, D.SEGMENT1
, BIC.OPTIONAL
, BIC.COMPONENT_SEQUENCE_ID
, BIC.WIP_SUPPLY_TYPE
, BIC.MUTUALLY_EXCLUSIVE_OPTIONS
FROM apps.BOM_BILL_OF_MATERIALS A, apps.MTL_SYSTEM_ITEMS_B B, apps.BOM_INVENTORY_COMPONENTS BIC, apps.MTL_SYSTEM_ITEMS_B D
, apps.HR_ORGANIZATION_UNITS HR
WHERE A.ASSEMBLY_ITEM_ID = B.INVENTORY_ITEM_ID
AND BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.ORGANIZATION_ID = HR.ORGANIZATION_ID
AND HR.NAME LIKE '%Master%'
AND B.SEGMENT1 = 'SLAB'
AND BIC.DISABLE_DATE IS NULL
AND BIC.COMPONENT_ITEM_ID = D.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = B.ORGANIZATION_ID
AND BIC.WIP_SUPPLY_TYPE = 2
AND (D.SEGMENT1 LIKE '%8' OR D.SEGMENT1 LIKE '%4'));

227 rows created.

SQL> commit;

Login to your Application and Go to Bills Of Material Super User





Choose Master Inventory Organization



All Oganizations to No
Import Routings No -- Since we don't use any routings.

Run the request.

BOM_INVENTORY_COMPS_INTERFACE should be cleaned after successfully update components.

BOM Open Interfaces

BOM Open Interfaces - An Oracle White Paper [185958.1]

After populating the interface tables, you can run the Bill and Routing Interface program.
The column, PROCESS_FLAG, indicates the current state of processing for a row in the interface table. Possible values for the column include:

1 - Pending 

2 - Assigned Succeeded 

3 - Assign/Validation Failed 

4 - Validation Succeeded 

7 - Import Succeeded 

You can use the following sql scripts to check for errors which will be located in the MTL_INTERFACE_ERRORS table to help resolve problems.

select table_name, column_name, organization_id, message_name, error_message
from mtl_interface_errors
where request_id = xxxx; (xxxx is the concurrent request id)
or
select table_name, column_name, organization_id, message_name, error_message
from mtl_interface_errors
where transaction_id = xxxx; (xxxx is the transaction id)

BOM_BILL_OF_MTLS_INTERFACE TABLE

This table temporarily stores bills of material header information. Each row in this table represents a unique Manufacturing or Engineering bill and is identified by the BILL_SEQUENCE_ID.

If you are creating an alternate bill of material you must also enter a value in the ALTERNATE_BOM_DESIGNATOR column.

If you enter something into REVISION, Oracle Bills of Materials will insert a record into
MTL_ITEM_REVISIONS.

If the bill you import references a common bill of material, you must enter a value in the
COMMON_ORGANIZATION_ID and COMMON_ASSEMBLY_ITEM_ID columns or you can enter a value in the COMMON_BILL_SEQUENCE_ID column.

The Bill and Routing Interface program derives the COMMON_BILL_SEQUENCE_ID from the
COMMON_ITEM_ID, COMMON_ORGANIZATION_ID, AND ALTERNATE_BOM_DESIGNATOR.

The required fields to create a Bill in the BOM_BILL_OF_MTLS_INTERFACE table are:

• PROCESS_FLAG
• ORGANIZATION_ID
• ASSEMBLY_ITEM_ID
• ASSEMBLY_TYPE
• TRANSACTION TYPE
• The PROCESS_FLAG needs to be 1 for pending.
• The ASSEMBLY_TYPE of the BOM; is 1 for manufacturing, and 2 for engineering.
• The TRANSACTION_TYPE needs to be 'create'.

The required fields to update a Bill using the BOM_BILL_OF_MTLS_INTERFACE table are:

• PROCESS_FLAG
• BILL_SEQUENCE_ID 

or

• ASSEMBLY_ITEM_ID or ITEM_NUMBER,
• ALTERNATE_BOM_DESIGNATOR, and
• ORGANIZATION_ID or ORGANIZATION_CODE
• TRANSACTION_TYPE
• The PROCESS_FLAG needs to be 1 for pending.
• The TRANSACTION_TYPE needs to be 'update'.

The required fields to delete a Bill using the BOM_BILL_OF_MTLS_INTERFACE table are:

• PROCESS_FLAG
• BILL_SEQUENCE_ID 

or

• ALTERNATE_BOM_DESIGNATOR,
• ASSEMBLY_ITEM_ID or ITEM_MUMBER, and
• ORGANIZATION_ID or ORGANIZATION_CODE
• TRANSACTION_TYPE

The PROCESS_FLAG needs to be 1 for pending.
The TRANSACTION_TYPE needs to be 'delete'.

In order to delete a Bill using the interface table, you must also insert a record into the
BOM_INTERFACE_DELETE_GROUPS table with the following values:

ENTITY_NAME (Must be BOM_BILL_OF MTLS_INTERFACE)
DELETE_GROUP_NAME (A new name or name of an existing Delete Group for bills)
DESCRIPTION (Description of the delete group)

BOM_INVENTORY_COMPS_INTERFACE

This table temporarily stores information about bill of material components and is shared by the Bills of Material and the Engineering Change Order open interfaces. The Engineering Change Order open interface will be available at a future date.


You cannot dynamically create locations for your subinventories using the interface tables. If you specify a value for the LOCATION_ID column, the location must already exist in Oracle Inventory.
 
You must specify a value in the ALTERNATE_BOM_DESIGNATOR column if you assign components to an alternate bill of material and have not entered a value for the BILL_SEQUENCE_ID column.

You can enter one reference designator and one substitute component for each component you enter.
Oracle Bills of Materials will insert these records into the tables, BOM_REFERENCE_DESIGNATORS and
BOM_SUBSTITUTE_COMPONENTS, respectively.

Oracle Bill of Material, enforces that only one row for a component in an operation is effective at a given time.




The required fields for creating components using the BOM_INVENTORY_COMPS_INTERFACE are:

• PROCESS_FLAG
• COMPONENT_ITEM_ID
• COMPONENT_SEQUENCE_ID - user should not be populating the component sequence id as it gets generated during the import
• OPERATION_SEQ_NUM
• EFFECTIVITY_DATE
• BILL_SEQUENCE_ID
• TRANSACTION_TYPE

The PROCESS_FLAG needs to be 1 for pending.

The TRANSACTION_TYPE needs to be 'create' for creating a component.

Note: the OPERATION_SEQ_NUM must be set to 1 if no routing has been defined.

The required fields for updating components using the BOM_INVENTORY_COMPS_INTERFACE are:

• COMPONENT_SEQUENCE_ID 

or

• BILL_SEQUENCE_ID,
• COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
• OPERATION_SEQ_NUM, and EFFECTIVITY_DATE 

or

• ASSEMBLY_ITEM_ID or ASSEMBLY_ITEM_NUMBER, ALTERNATE_BOM_DESIGNATOR,
• ORGANIZATION_ID or ORGANIZATION_CODE,
• COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
• OPERATION_SEQ_NUM, and EFFECTIVITY_DATE

You also need to enter:

• PROCESS_FLAG
• TRANSACTION_TYPE

If the component is unimplemented, you cannot update the record.

If you want to update the OPERATION_SEQ_NUM or EFFECTIVITY_DATE, then you must fill in the NEW_OPERATION_SEQ_NUM or NEW_EFFECTIVITY_DATE column.

The PROCESS_FLAG needs to be 1 for pending.

The TRANSACTION_TYPE needs to be 'update' for updating a component.

The required fields for deleting components using the BOM_INVENTORY_COMPS_INTERFACE are:

• COMPONENT_SEQUENCE_ID

or

• BILL_SEQUENCE_ID,
• COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
• OPERATION_SEQ_NUM, and EFFECTIVITY_DATE

or

• ASSEMBLY_ITEM_ID or ASSEMBLY_ITEM_NUMBER, ALTERNATE_BOM_DESIGNATOR,
• ORGANIZATION_ID or ORGANIZATION_CODE,
• COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
• OPERATION_SEQ_NUM, and EFFECTIVITY_DATE

You also need to enter:

• PROCESS_FLAG
• TRANSACTION_TYPE

If the component is unimplemented, you cannot delete the record.

The PROCESS_FLAG needs to be 1 for pending.

The TRANSACTION_TYPE needs to be 'delete'.

In order to delete a component using the interface table, you must also insert a record into the
BOM_INTERFACE_DELETE_GROUPS table with the following values:

ENTITY_NAME (Must be BOM_INVENTORY_COMPS_INTERFACE)
DELETE_GROUP_NAME (A new name or name of an existing Delete Group for bills)
DESCRIPTION (Description of the delete group)

RMAN Duplicate Database From RAC ASM To RAC ASM [ID 461479.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.4 - Release: 10.1 to 10.2
Information in this document applies to any platform.

Goal

RMAN Duplicate Database From RAC ASM to RAC ASM

Solution

Assumptions

This note assumes the following:

You are using recovery catalog database catdb

The target database trgt is on host1 and contains ASM datafiles and online logs in diskgroup +DISK1

You want to duplicate the target to database dupdb on remote host host2.

host2 has diskgroup +DISK2

You want to store the datafiles for dupdb to +DISK2

You want to store two controlfiles in +DISK2

The backups and archivelogs created by host1 are accessible by host2

Procedure

1) Create an initialization parameter for auxiliary instance by copying the target database initialization parameter file. Change the parameters as follows:

Set DB_NAME to the new database name dupdb

Set CONTROL_FILES to store two copies of the control file in +DISK2

Set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to convert the datafile and online log file names from +DISK1 to +DISK2

Set any other initialization parameters that end in _DEST, such as DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_DEST_n, to reference +DISK2

Comment all the RAC related parameters in the dupdb parameter file. We will re-enable them once the duplicate is completed.
2) For an active production RAC environment, you must use a SET UNTIL clause, otherwise the archivelogs would not have been backed up and the duplicate would fail with RMAN-06025.

To get the sequence number, you need to look at the following:

   RMAN> list backup of archivelog all;

We need to determine the most recent archivelog backed up from each thread, and then take the least recent of these.

Example

Parameter file (init.ora) would include:

DB_NAME=dupdb
CONTROL_FILES=+DISK2,+DISK2
DB_FILE_NAME_CONVERT=+DISK1,+DISK2
LOG_FILE_NAME_CONVERT=+DISK1,+DISK2

Create an SPFILE from the parameter file, and start the auxiliary instance:


SQL> CONNECT SYS/oracle@dupdb;
SQL> CREATE SPFILE FROM PFILE='location of auxiliary instance pfile';
SQL> STARTUP NOMOUNT;

Then run the following RMAN commands to duplicate the database:


RMAN> CONNECT TARGET /;
RMAN> CONNECT CATALOG rman/cat@catdb;
RMAN> CONNECT AUXILIARY SYS/oracle@dupdb;
RMAN> run {
set until sequence x thread y;
DUPLICATE TARGET DATABASE TO dupdb;
}

 ==> replace the sequence and thread number as appropriate


When the DUPLICATE command completes, the duplicate database is created, with datafiles, online logs and control files in ASM disk group +DISK2.

Shutdown the database and add all the RAC related parameters and then start the database.
Note:-  The only difference between duplicate from RAC ASM and standalone ASM is not to perform the last step i.e. adding of the RAC related parameters.