Month: June 2020

HTTP 404 error after logging into Oracle EBS 12.2.6 url

Issue Description

We recently upgraded the Oracle EBS Version to 12.2.6 from 12.1.3. After Upgrade, one user reported he is getting erro HTTP 404 when he login to the application url.

Cause:

User has set his preferences to load a Custom Start Page after login. The custom html was not available after the upgrade.Hence he is getting error HTTP 404 as in screenshot. He is unable to reset the preferences as the page is not loading.

Solution:

In this case, only option is to modify the profile option ‘Application Start Page’ for the specific user  to a correct value.

Oracle EBS 12.2.x: ADOP error during Cutover phase

Background of the issue: I was applying hrblobal driver in a multi-node Production environment. Our Production application EBS has one Primary node and one DMZ Node.


After applying hrglobal driver using the online patching cycle, Cutover phase thrown below error message


==

Applying patch(es): <6390830> on node EBSDMZ failed.
If you choose to proceed with cutover, node EBSDMZ will be marked as abandoned.
Do you want adop to continue processing on completed nodes [y/n]? n
    As per user choice, adop will not proceed further with processing.


==


If we Provide input as ‘Y’ above, EBSDMZ node will be abandoned and we need to register the DMZ node again.




Solution:


1.Apply Patch on DMZ node using options=nodb and restart the cutover phase


On DMZ Node
=====
Source the patch filesystem
adop phase=apply patchtop=$PER_TOP/patch/115 patches=driver:hrglobal.drv allnodes=NO action=nodb options=forceapply


2. Restart Cutover phase on Master node


What are the features of BIGFILE Tablespace in Oracle Database?

Bigfile Tablespace is introduced from Oracle 10g.A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile.


The benefits of bigfile tablespaces are the following:

A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.



Bigfile tablespaces can reduce the number of datafiles needed for a database. 
An additional benefit is that the DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted to reduce the amount of SGA space required for datafile information and the size of the control file.


Bigfile tablespaces simplify database management by providing datafile transparency. 
SQL syntax for the ALTER TABLESPACE statement lets you perform operations on tablespaces, 
rather than the underlying individual datafiles.


Bigfile tablespaces are intended to be used with Automatic Storage Management (ASM) or other logical volume managers that supports striping or RAID, and dynamically extensible logical volumes.


Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel query execution and RMAN backup parallelization.




Creating a Bigfile Tablespace




To create a bigfile tablespace, specify the BIGFILE keyword of the CREATE TABLESPACE statement (CREATE BIGFILE TABLESPACE …)


CREATE BIGFILE TABLESPACE bigtbs 
    DATAFILE ‘/u02/oracle/data/bigtbs01.dbf’ SIZE 50G
…




Identifying a Bigfile Tablespace


The following views contain a BIGFILE column that identifies a tablespace as a bigfile tablespace:


DBA_TABLESPACES


USER_TABLESPACES


V$TABLESPACE

ORA-32771: Cannot Add File To Bigfile Tablespace

When trying to add a datafile to a tablespace , got the error – ORA-32771: cannot add file to bigfile tablespace.


SQL> ALTER TABLESPACE BIG_TBSP1 add datafile ‘+DATA/df01.dbf’ size 130G;
ALTER TABLESPACE BIG_TBSP1 add datafile ‘+DATA/df01.dbf’ size 130G;
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace


SOLUTION:


A bigfile tablespace can contain only one datafile and no other datafile can be added to that.


SQL> select BIGFILE from dba_tablespaces where tablespace_name=’BIG_TBSP1’;


BIGFILE
———————– —
YES


How to increase space in Bigfile Tablespace?




BIGFILE tablespace stores its data in a single datafile with a much larger capacity.


We can resize the size of the datafile in BIGFILE tablespace using ALTER DATABASE Command




ALTER DATABASE DATAFILE ‘/+DATA/df0101.dbf’ RESIZE 180G;


Since BIGFILE Tablespace has only one datafile, there is no need to identify the datafile and increase its size.
We can use ALTER TABLESPACE command to resize at the tablespace level.


ALTER TABLESPACE BIG_TBSP1 RESIZE 180G;

How to Use Logon Trigger to trace sessions connected to Oracle database?

To start tracing:


create or replace trigger user_logon_trg
after logon on database
begin
if USER = ‘xxxx’ then
execute immediate
‘Alter session set events ”10046 trace name context forever, level 8”’;
end if;
end;
/


/* Login a new session as User ‘xxxx’ and execute your selects to be traced */




NOTE: The following syntax can also be used within the if logic to also get the user name or to add more granularity by specifying a host name where the connection originated.


IF SYS_CONTEXT(‘USERENV’,’SESSION_USER’) = ‘<USER_NM>’ AND SYS_CONTEXT(‘USERENV’,’HOST’) = ‘<HOST_NM>’ THEN


To stop tracing: via LogOff Trigger (needs to be created before logging off)


create or replace trigger user_logoff_trg
before logoff on database
begin
if USER = ‘xxxx’ then
execute immediate
‘Alter session set events ”10046 trace name context off”’;
end if;
end;
/


What is the difference between Switchover and Failover in Oracle Dataguard?

A switchover means just switching roles between the primary database and standby db.
nswitchover, the primary database chnaged to a standby role, and the standby database changed to the primary role.
This is typically done for planned maintenance of the primary db server.


A failover is when the primary database fails and one of the standby databases is 
transitioned to take over the primary role. Failover is performed only in the event 
of a catastrophic failure of the primary database, and there is no possibility of 
recovering the primary database in a timely manner. Failover may or may not result 
in data loss depending on the protection mode in effect at the time of the failover.

How to enable ArchiveLog mode in Oracle Database RAC Environment?

Steps to enable Archivelog mode in RAC environment:


The following steps need to be taken to enable archive logging in a RAC database environment:


1. Shutdown immediate all database instances
$ srvctl stop database -d <db_unique_name>


2. Startup database in mount mode
$ srvctl start database -d <db_unique_name> -o mount


3. Enable archive logging
$ sqlplus / as sysdba
sql> alter database archivelog;
sql> exit;


4. Stop database
$ srvctl stop database -d <db_unique_name>


5.Start all database instances
$ srvctl start database -d <db_unique_name>


6.Verify archiving is enabled/disabled 
sql> archive log list;

How to mirror database Privileges of a Oracle database user to another database user?

Script for Mirroring Privileges of a user:


Scenario: A Database schema named “TEST2” is created and business wants to grant the privileges for the user same as already existing Database user “TEST1”.


Run the below queries as SYS or any Privileged user to get the DDL commands in Oracle for TEST1 user.


SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’TEST1′) FROM DUAL;


 SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’TEST1′) FROM DUAL;


 SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’TEST1′) FROM DUAL;

 The output will be a sequence of grant commands related to privileges assigned to TEST1 user. Now replace the string TEST1 with new user name “TEST2” and run the commands.

 This will grant all privileges same as TEST1 user to TEST2 user in Oracle Database.