Steps to Configure database in archive log mode

Please follow below steps to configure database in archive log mode.

Check if the database running archive log mode or not by using following query.

SQL> select NAME,LOG_MODE from v$database;

NAME      LOG_MODE
——— ————
RMANDV1   NOARCHIVELOG

There are two init.ora parameters we need to change before configuring database in archive log mode.

1)LOG_ARCHIVE_DEST —/u01/app/archive/rmandv1
2)LOG_ARCHIVE_FORMAT —’rmandv1%t_%s_%r.arc’

alter system set LOG_ARCHIVE_DEST=’/d01/app/archive/rmandv1′ scope =both;

alter system set LOG_ARCHIVE_FORMAT=’rmandv1%t_%s_%r.arc’ scope =spfile;

Step1)Shutdown the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step2)Start  up a new instance and mount, but do not open the database.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  180355072 bytes
Fixed Size                  2094896 bytes
Variable Size              88082640 bytes
Database Buffers           83886080 bytes
Redo Buffers                6291456 bytes
Database mounted.

Step3)Put the database in archive log mode

SQL> alter database archivelog;

Database altered

Now open the database

SQL> alter database open;

Database altered.

check now if your database is in archive log mode.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/archive/rmandv1
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

How to find Oracle Application connection problems

If you can't login into EBS via logon page you have few tools to find the problems:


http://<server>.<domain>:<PORT>/OA_HTML/ServletPing

http://<server>.<domain>:<PORT>/OA_HTML/jsp/fnd/aoljtest.jsp

http://<server>.<domain>:<PORT>/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE

http://<server>.<domain>:<PORT>

http://<server>.<domain>:<PORT>/OA_MEDIA/FNDLOGOL.gif

http://<server>.<domain>:<PORT>/forms/frmservlet

http://<hostname.domainname>:<port>/OA_HTML/fndvald.jsp?username=sysadmin&password=<sysadmin_password>

Good luck.

Compile 10g Forms/Reports Takes a Very Long Time against 11g database

Compile 10g Forms/Reports Takes a Very Long Time against 11g database

Hi All

Here the new issue that I found during upgrade to R12 with 11g Database.
As you know the biggest part of patches are generating Forms and Reports in the end of patch installation. This procedure was very slow and takes a lot of time.

After some search in Metalink and Google I found the Doc ID 880660.1 - Compilation Against a 11g Database Hangs or Takes a Very Long Time (Thanks to Harry Tieb).

Action Plan 

1. Apply the database Patch 8560951 on top of your Database. 
   The Patch 8560951 brings modifications in a sensible area and it is needed to use_FIX_CONTROL to enable the fix. 

*** This patch is already included in higher database versions (e.g. 10.2.0.5, 11.2.0.2). For these it's not necessary to install the patch.  But, _FIX_CONTROL='8560951:ON' still needs to be set as the fix is disabled by default. 

2. ALTER SYSTEM SET "_FIX_CONTROL"='8560951:ON'; - This will enable the fix in memory. 
OR add this parameter to init.ora file and restart the database.

3. Compile the Forms/Reports again.
    If needed to restore things as they were, you can similarly turn the fix off with:
    ALTER SYSTEM SET "_FIX_CONTROL"='8560951:OFF';
OR 
Use the following workaround: 

1. Connect to the DB with SQL*Plus as the user who compiles the Forms application 
2. Use the following command to create a synonym all_objects with:
    create synonym all_objects for sys.dba_objects; 
    If it's not working, grant SELECT privelege on sys.dba_objects to the user who will be  compiling the form.
3. Compile the Forms/Reports again.
    If you want at the end, you can drop this synonym with:  drop synonym all_objects;

Taken from http://dba-story.blogspot.co.il/2012/02/compile-10g-formsreports-takes-very.html

WebADI Run-time error'1004' Method 'VBProject' of object '_Workbook' failed

ISSUE:

Run-time error '1004':

Method 'VBProject' of Object'_Workbook' Failed
this issue reproduce when downloading the excel sheet via webADI .

SOLUSTION:
In Excel 2003:
go to "Tools" > "Macro" > "Security" and select the "Trusted Sources" tab.  Check the box next to "Trust access to Visual Basic Project".
(Note: The box for "Trust all installed add-ins and templates" should also be checked.)

In Excel 2007, use the following navigation:

·         Click on the Office button in the upper left corner

·         Click on the Excel Options button

·         On the left, click on the Trust Center

·         Click on the Trust Center Settings button

·         On the left, click on Macro Settings

·         Click on "Trust access to the VBA project object model"

Ref: 376013.1
       406526.1      

Turn off user password expiration in ORACLE

Hi,

First of all you can check if user password not expire:
 
SELECT LIMIT FROM dba_profiles WHERE resource_name ='FAILED_LOGIN_ATTEMPTS' 
and PROFILE = (select profile from dba_users where username = 'SCOTT');

Replace scott with user you are interested in , if it is unlimited or no rows selected it means it wont expire.

You can disable passsword expiration for specified user:

SELECT PROFILE FROM dba_users WHERE username = 'User you are interested in';

When you know profile name, you can change it:

ALTER PROFILE <profile_name> LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER PROFILE <profile_name> LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Best regards.

SQL SERVER – FIX : Error 15023: User already exists in current database.

February 15, 2007 by pinaldave

Error 15023: User already exists in current database.

1) This is the best Solution.
First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan.
USE YourDB
GO
EXEC sp_change_users_login 'Report'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘ColdFusion’ is UserName, ‘cf’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.
USE YourDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'ColdFusion', NULL, 'cf'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified
USE YourDB
GO
EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion'
GO

2) If login account has permission to drop other users, run following T-SQL in Query Analyzer. This will drop the user.
USE YourDB
GO
EXEC sp_dropuser 'ColdFusion'
GO

Create the same user again in the database without any error.

How to create Backup & Delete all rows from a table

Hello,

Sometime you should make backup for a table and then delete all from original table.
You can do it which next steps.

First of all you have check data in original table:
1. select * from CHOICE_VALUES

When you know count of rows this is a time for backup table:
2. create table CHOICE_VALUES_backup as select * from  CHOICE_VALUES;

Delete old data from original table.
3. delete from CHOICE_VALUES;
4. commit;

Good luck....